我有一個名為的MySQL表users，它具有可為n空的列和x行。
如果任何行在該列中有值，我需要獲取列的名稱。
例子：
用戶表
-----------------------------------------------
| id | ref | col1 | col2 | col3 | col4 | col5 |
-----------------------------------------------
| 01 | 100 | null | null | null | null | null |
-----------------------------------------------
| 02 | 100 | qwer | null | null | null | null |
-----------------------------------------------
| 03 | 100 | qwer | plod | null | null | null |
-----------------------------------------------
| 04 | 100 | trye | qwer | plod | null | null |
-----------------------------------------------
| 05 | 101 | plod | trye | qwer | null | trye |
-----------------------------------------------
在此表中，列col4在任何行中都沒有值。因此，在獲取列名時，我需要排除該列名。
所需的輸出：
id, ref, col1, col2, col3, col5
我嘗試過一些查詢，但有語法錯誤：
#syntax error
SHOW COLUMNS FROM `users` WHERE COLUMN is NOT null;
我的另一個想法是，找到列最少的null行。（所以它將是5上表中帶有 id 的行。）
目前我正在使用 PHP 獲取所有行和行程以獲取結果。尋找直接的 MySQL 查詢。
任何幫助將不勝感激。
uj5u.com熱心網友回復：
您可以生成一個稍后可以執行的查詢：
select concat("select id ",CASE count(col1)
WHEN 0 THEN ""
ELSE ", col1"
END, " from users")
from users
可能的結果：
select id , col1 from users
或者
select id from users
對于第二部分，如果 0 可以：
select concat("select id ",CASE count(col1)
WHEN (select LEAST(count(col1),
count(col2),
count(col3))
from users) THEN ", col1"
ELSE ""
END,
CASE count(col2)
WHEN (select LEAST(count(col1),
count(col2),
count(col3))
from users) THEN ", col2"
ELSE ""
END,
" from users")
from users
結果：
select id , col3 from users
如果您想排除 0，您可以將這樣的內容傳遞給LEAST()：
select (case count(col1) when 0 Then 9999999999 Else count(col1) End)
from users
抱歉，我沒有意識到您在寫之前正在查看行。這適用于列，但在開始查看數量之后，您可能會了解如何將其重寫為行nulls：
select tmp.id,
x y as total
from (select id,
col1,
col2,
case when col1 is null then 0 else 1 end x,
case when col2 is null then 0 else 1 end y
from users) tmp
group by tmp.id
然后在最少的行nulls：
with tmp2 as
(select tmp.id,
x y as total
from (select id,
col1,
col2,
case when col1 is null then 1 else 0 end x,
case when col2 is null then 1 else 0 end y
from users) tmp
group by tmp.id)
select *
from tmp2
where total = (select min(total) from tmp2)
uj5u.com熱心網友回復：
嘗試這樣的事情：
SELECT col_name FROM
(
SELECT 'col1' AS col_name, count(*) as amount
FROM users WHERE col1 is not null
UNION ALL
SELECT 'col2' AS col_name, count(*) as amount
FROM users WHERE col2 is not null
UNION ALL
SELECT 'col3' AS col_name, count(*) as amount
FROM users WHERE col3 is not null
....
// repeat for all columns
)
WHERE amount > 0
uj5u.com熱心網友回復：
;with cte as(
select
count( case when id is null then null else 1 end) as id
, count( case when ref is null then null else 1 end) as ref
, count( case when col1 is null then null else 1 end) as col1
, count( case when col2 is null then null else 1 end) as col2
, count( case when col3 is null then null else 1 end) as col3
, count( case when col4 is null then null else 1 end) as col4
, count( case when col5 is null then null else 1 end) as col5
from table_name
) select 'id ' from cte where id = 0
union select 'ref ' from cte where ref = 0
union select 'col1' from cte where col1 =0
union select 'col2' from cte where col2 =0
union select 'col3' from cte where col3 =0
union select 'col4' from cte where col4 =0
union select 'col5' from cte where col5 =0
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/422493.html
標籤：
上一篇：SQL-根據日期映射行