http://book.naver.com/bookdb/book_detail.nhn?bid=3105641 覦豬
SELECT REPLACE(REPLACE(REPLACE('<start> SELECT ''<col>'' as colname,
COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as
VARCHAR) as minval, SUM(CASE WHEN <col> = minval THEN freq ELSE 0 END)
as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN
<col> = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =
1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT <col>, COUNT(*) as freq
FROM <tab> GROUP BY <col>) osum CROSS JOIN (SELECT MIN(<col>) as minval,
MAX(<col>) as maxval, SUM(CASE WHEN <col> IS NULL THEN 1 ELSE 0 END) as
freqnull FROM (SELECT <col> FROM <tab>) osum) summary',
'<col>', column_name),
'<tab>', table_name),
'<start>',
(CASE WHEN ordinal_position = 1 THEN ''
ELSE 'UNION ALL' END))
FROM (SELECT table_name, column_name, ordinal_position
FROM information_schema.columns
WHERE table_name = 'orders') a

-- 貎朱Μ襯 ろ 蟆郁骸 .. 襯 ろ覃...貉殊  蠍磯蓋 糾
SELECT 'orderid' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN orderid = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  orderid = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT orderid, COUNT(*) as freq  FROM orders GROUP BY orderid) osum CROSS JOIN (SELECT MIN(orderid) as minval,  MAX(orderid) as maxval, SUM(CASE WHEN orderid IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT orderid FROM orders) osum) summary
UNION ALL SELECT 'customerid' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN customerid = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  customerid = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT customerid, COUNT(*) as freq  FROM orders GROUP BY customerid) osum CROSS JOIN (SELECT MIN(customerid) as minval,  MAX(customerid) as maxval, SUM(CASE WHEN customerid IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT customerid FROM orders) osum) summary
UNION ALL SELECT 'campaignid' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN campaignid = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  campaignid = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT campaignid, COUNT(*) as freq  FROM orders GROUP BY campaignid) osum CROSS JOIN (SELECT MIN(campaignid) as minval,  MAX(campaignid) as maxval, SUM(CASE WHEN campaignid IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT campaignid FROM orders) osum) summary
UNION ALL SELECT 'orderdate' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN orderdate = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  orderdate = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT orderdate, COUNT(*) as freq  FROM orders GROUP BY orderdate) osum CROSS JOIN (SELECT MIN(orderdate) as minval,  MAX(orderdate) as maxval, SUM(CASE WHEN orderdate IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT orderdate FROM orders) osum) summary
UNION ALL SELECT 'city' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN city = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  city = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT city, COUNT(*) as freq  FROM orders GROUP BY city) osum CROSS JOIN (SELECT MIN(city) as minval,  MAX(city) as maxval, SUM(CASE WHEN city IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT city FROM orders) osum) summary
UNION ALL SELECT 'state' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN state = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  state = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT state, COUNT(*) as freq  FROM orders GROUP BY state) osum CROSS JOIN (SELECT MIN(state) as minval,  MAX(state) as maxval, SUM(CASE WHEN state IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT state FROM orders) osum) summary
UNION ALL SELECT 'zipcode' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN zipcode = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  zipcode = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT zipcode, COUNT(*) as freq  FROM orders GROUP BY zipcode) osum CROSS JOIN (SELECT MIN(zipcode) as minval,  MAX(zipcode) as maxval, SUM(CASE WHEN zipcode IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT zipcode FROM orders) osum) summary
UNION ALL SELECT 'paymenttype' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN paymenttype = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  paymenttype = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT paymenttype, COUNT(*) as freq  FROM orders GROUP BY paymenttype) osum CROSS JOIN (SELECT MIN(paymenttype) as minval,  MAX(paymenttype) as maxval, SUM(CASE WHEN paymenttype IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT paymenttype FROM orders) osum) summary
UNION ALL SELECT 'totalprice' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN totalprice = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  totalprice = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT totalprice, COUNT(*) as freq  FROM orders GROUP BY totalprice) osum CROSS JOIN (SELECT MIN(totalprice) as minval,  MAX(totalprice) as maxval, SUM(CASE WHEN totalprice IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT totalprice FROM orders) osum) summary
UNION ALL SELECT 'numorderlines' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN numorderlines = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  numorderlines = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT numorderlines, COUNT(*) as freq  FROM orders GROUP BY numorderlines) osum CROSS JOIN (SELECT MIN(numorderlines) as minval,  MAX(numorderlines) as maxval, SUM(CASE WHEN numorderlines IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT numorderlines FROM orders) osum) summary
UNION ALL SELECT 'numunits' as colname,  COUNT(*) as numvalues, MAX(freqnull) as freqnull, CAST(MIN(minval) as  VARCHAR) as minval, SUM(CASE WHEN numunits = minval THEN freq ELSE 0 END)  as numminvals, CAST(MAX(maxval) as VARCHAR) as maxval, SUM(CASE WHEN  numunits = maxval THEN freq ELSE 0 END) as nummaxvals, SUM(CASE WHEN freq =  1 THEN 1 ELSE 0 END) as numuniques FROM (SELECT numunits, COUNT(*) as freq  FROM orders GROUP BY numunits) osum CROSS JOIN (SELECT MIN(numunits) as minval,  MAX(numunits) as maxval, SUM(CASE WHEN numunits IS NULL THEN 1 ELSE 0 END) as  freqnull FROM (SELECT numunits FROM orders) osum) summary

/*
colname       numvalues   freqnull    minval                         numminvals  maxval                         nummaxvals  numuniques
------------- ----------- ----------- ------------------------------ ----------- ------------------------------ ----------- -----------
orderid       192983      0           999992                         1           1643157                        1           192983
customerid    189560      0           0                              3424        189559                         1           189559
campaignid    239         0           2001                           5           2239                           4           24
orderdate     2541        0           10  4 2009 12:00AM             181         09 20 2016 12:00AM             2           0
city          12825       17          #07-01 FONTANA HGTS            1           ZURICH                         5           6318
state         92          1119        .                              2           YU                             2           14
zipcode       15579       144         -                              1           Z5B2T                          1           5954
paymenttype   6           0           ??                             313         VI                             77017       0
totalprice    7653        0           0.00                           9128        9848.96                        1           4115
numorderlines 41          0           1                              139561      150                            1           14
numunits      142         0           1                              127914      19527                          1           55
*/