Untitled query #14489

This query is marked as a draft This query has been published by ԱշոտՏՆՂ.
SQL

#SELECT DISTINCT log_type,
#                log_action
#FROM logging
#ORDER BY log_type,
#         log_action;

WITH Admins AS
  (SELECT user_name,
          user_id
   FROM user
   JOIN user_groups ON user_id = ug_user
   WHERE ug_group = 'sysop'),
     AdminLogs AS
  (SELECT log_type,
          log_action,
          actor_user
   FROM logging
   JOIN actor ON actor_id = log_actor
   WHERE actor_user IN
       (SELECT admins.user_id
        FROM Admins AS admins)
     AND log_timestamp >= DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, '%Y%m%d%H%i%S')),
   TEST AS (
SELECT user_name,
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'block'
     AND log_action = 'NULL'
     AND actor_user = user_id) 'block\nNULL',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'create'
     AND log_action = 'NULL'
     AND actor_user = user_id) 'create\nNULL',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'delete'
     AND log_action = 'NULL'
     AND actor_user = user_id) 'delete\nNULL',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'newusers'
     AND log_action = 'NULL'
     AND actor_user = user_id) 'newusers\nNULL',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'protect'
     AND log_action = 'NULL'
     AND actor_user = user_id) 'protect\nNULL',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'managetags'
     AND log_action = 'activate'
     AND actor_user = user_id) 'managetags\nactivate',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'growthexperiments'
     AND log_action = 'addlink'
     AND actor_user = user_id) 'growthexperiments\naddlink',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'newusers'
     AND log_action = 'autocreate'
     AND actor_user = user_id) 'newusers\nautocreate',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'block'
     AND log_action = 'block'
     AND actor_user = user_id) 'block\nblock',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'newusers'
     AND log_action = 'byemail'
     AND actor_user = user_id) 'newusers\nbyemail',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'contentmodel'
     AND log_action = 'change'
     AND actor_user = user_id) 'contentmodel\nchange',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'abusefilter'
     AND log_action = 'create'
     AND actor_user = user_id) 'abusefilter\ncreate',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'create'
     AND log_action = 'create'
     AND actor_user = user_id) 'create\ncreate',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'managetags'
     AND log_action = 'create'
     AND actor_user = user_id) 'managetags\ncreate',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'newusers'
     AND log_action = 'create'
     AND actor_user = user_id) 'newusers\ncreate',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'newusers'
     AND log_action = 'create2'
     AND actor_user = user_id) 'newusers\ncreate2',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'delete'
     AND log_action = 'delete'
     AND actor_user = user_id) 'delete\ndelete',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'managetags'
     AND log_action = 'delete'
     AND actor_user = user_id) 'managetags\ndelete',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'delete'
     AND log_action = 'delete_redir'
     AND actor_user = user_id) 'delete\ndelete_redir',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'delete'
     AND log_action = 'event'
     AND actor_user = user_id) 'delete\nevent',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'massmessage'
     AND log_action = 'failure'
     AND actor_user = user_id) 'massmessage\nfailure',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'gblblock'
     AND log_action = 'gblock2'
     AND actor_user = user_id) 'gblblock\ngblock2',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'spamblacklist'
     AND log_action = 'hit'
     AND actor_user = user_id) 'spamblacklist\nhit',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'merge'
     AND log_action = 'merge'
     AND actor_user = user_id) 'merge\nmerge',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'abusefilter'
     AND log_action = 'modify'
     AND actor_user = user_id) 'abusefilter\nmodify',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'protect'
     AND log_action = 'modify'
     AND actor_user = user_id) 'protect\nmodify',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'move'
     AND log_action = 'move'
     AND actor_user = user_id) 'move\nmove',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'protect'
     AND log_action = 'move_prot'
     AND actor_user = user_id) 'protect\nmove_prot',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'move'
     AND log_action = 'move_redir'
     AND actor_user = user_id) 'move\nmove_redir',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'contentmodel'
     AND log_action = 'new'
     AND actor_user = user_id) 'contentmodel\nnew',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'newusers'
     AND log_action = 'newusers'
     AND actor_user = user_id) 'newusers\nnewusers',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'upload'
     AND log_action = 'overwrite'
     AND actor_user = user_id) 'upload\noverwrite',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'patrol'
     AND log_action = 'patrol'
     AND actor_user = user_id) 'patrol\npatrol',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'protect'
     AND log_action = 'protect'
     AND actor_user = user_id) 'protect\nprotect',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'block'
     AND log_action = 'reblock'
     AND actor_user = user_id) 'block\nreblock',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'renameuser'
     AND log_action = 'renameuser'
     AND actor_user = user_id) 'renameuser\nrenameuser',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'timedmediahandler'
     AND log_action = 'resettranscode'
     AND actor_user = user_id) 'timedmediahandler\nresettranscode',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'delete'
     AND log_action = 'restore'
     AND actor_user = user_id) 'delete\nrestore',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'upload'
     AND log_action = 'revert'
     AND actor_user = user_id) 'upload\nrevert',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'delete'
     AND log_action = 'revision'
     AND actor_user = user_id) 'delete\nrevision',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'rights'
     AND log_action = 'rights'
     AND actor_user = user_id) 'rights\nrights',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'growthexperiments'
     AND log_action = 'setmentor'
     AND actor_user = user_id) 'growthexperiments\nsetmentor',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'massmessage'
     AND log_action = 'skipbadns'
     AND actor_user = user_id) 'massmessage\nskipbadns',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'massmessage'
     AND log_action = 'skipnouser'
     AND actor_user = user_id) 'massmessage\nskipnouser',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'thanks'
     AND log_action = 'thank'
     AND actor_user = user_id) 'thanks\nthank',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'block'
     AND log_action = 'unblock'
     AND actor_user = user_id) 'block\nunblock',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'protect'
     AND log_action = 'unprotect'
     AND actor_user = user_id) 'protect\nunprotect',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'tag'
     AND log_action = 'update'
     AND actor_user = user_id) 'tag\nupdate',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'upload'
     AND log_action = 'upload'
     AND actor_user = user_id) 'upload\nupload',
  (SELECT count(*)
   FROM AdminLogs
   WHERE log_type = 'gblblock'
     AND log_action = 'whitelist'
     AND actor_user = user_id) 'gblblock\nwhitelist'
     FROM Admins)
     
SET @query = (
    SELECT CONCAT(
        'SELECT ',
        GROUP_CONCAT(COLUMN_NAME),
        ' FROM your_table HAVING ',
        GROUP_CONCAT(COLUMN_NAME, ' > 0 AND '),
        '1 = 1;'
    ) AS sql_query
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'your_table'
);

-- Execute the dynamic SQL query
PREPARE dynamic_sql FROM @query;
EXECUTE dynamic_sql;
DEALLOCATE PREPARE dynamic_sql;
SELECT * FROM TEST;
#SELECT user_name,
#       user_id
#FROM USER
#JOIN user_groups ON user_id = ug_user
#WHERE ug_group = 'sysop' #SELECT DISTINCT log_action #FROM hywiki_p.logging
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
