Database SQL queries-CodePudding

Nine tables, respectively is a to I, the 9 form fields, respectively is the date and the name of the table, the query results is the number of rows, have another piece of z table summarizes all the name of the table, now is the result of the query fields for date, name of the table, the set of rows, such as I write so select a. d. ate, z.t ablename, count the from a, b, c, d, e, f, g, h, I, z where a. d. ate=to_timestamp (' 20170910 ', 'yyyymmdd') and z.t ablename in (select tablename from tmp_m_tablename); So how to change to correct?

CodePudding user response:

refer to the original poster linkbin response:

now as a result of the query to the field to date, the name of the table, the set of rows

To see is not very good

CodePudding user response:

Also understand that we didn't, but you write COUNT in the SQL need to COUNT (*)

CodePudding user response:

So you write should be complains group by problems;
Suggest you wrote the Union (the nine tables Count results (few form the Union statement copy once good)
SELECT the date, the tablename, COUNT (*) FROM WHERE a date=to_timestamp (' 20170910 ', 'yyyymmdd') GROUP BY the date, the table name UNION ALL
SELECT the date, the tablename, COUNT (*) FROM B WHERE the date=to_timestamp (' 20170910 ', 'yyyymmdd') GROUP BY the date, the table name UNION ALL

CodePudding user response:

According to your writing, the end result to be amplified, because similar to CROSS the JOIN, and there is no associated with Z table, just put Z all rows in a table query, is also a CROSS JOIN.

I think you can refer to the above chenghangstar writing, or write

 
SELECT DATE, 'a' AS the name of the table, the COUNT (*) AS the number of rows FROM a DATE WHERE=CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'b' AS the name of the table, the COUNT (*) AS the number of rows FROM the WHERE DATE=b CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'c' AS the name of the table, the COUNT (*) AS the number of rows FROM the WHERE DATE=c CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'd' AS the name of the table, the COUNT (*) AS the number of rows FROM d WHERE DATE=CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'e' AS the name of the table, the COUNT (*) AS the number of rows WHERE the FROM e DATE=CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'f' AS the name of the table, the COUNT (*) AS the number of rows FROM the WHERE DATE=f CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'g' AS the name of the table, the COUNT (*) AS the number of rows WHERE DATE FROM g=CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'h' AS the name of the table, the COUNT (*) AS the number of rows FROM h WHERE DATE=CAST (' 20180101 'AS the DATE) GROUP BY DATE 
UNION ALL 
SELECT DATE, 'I' AS the name of the table, the COUNT (*) AS the number of rows FROM h WHERE DATE=CAST (' 20180101 'AS the DATE) GROUP BY DATE