mysql - 根据sqlserver/ssis中的非空条件将表拆分为多个表

我有一种情况，如下所示，我有一个输入表表（动态表的列数不固定），并且需要基于非空值获取多个表

输入表

ID  Name    Mobile  Year    value
1   john    1238769 2001    35
2   tommy   3423456 2001    56
3   smith   8761934 2007    65
4   NULL    4783921 2005    78
5   robert  8549543 2008    18
6   mary    5648404 2011    40
7   NULL    6729113 2003    59
8   NULL    NULL    2006    10
9   cathy   NULL    2010    35
10  jessi   NULL    2012    45

所以我需要基于非空的下表

输出表1

ID  Name    Mobile  Year    value
1   john    1238769 2001    35
2   tommy   3423456 2001    56
3   smith   8761934 2007    65
5   robert  8549543 2008    18
6   mary    5648404 2011    40

输出表2

ID  Mobile  Year    value
4   4783921 2005    78
7   6729113 2003    59

输出表3

ID  Name    Year    value
9   cathy   2010    3578
10  jessi   2012    45

最后输出表4

ID  Year    value
8   2006    10

最佳答案

INSERT INTO OutputTable1
SELECT yourtable.*
FROM yourtable
WHERE Name IS NOT NULL and Mobile IS NOT NULL

INSERT INTO OutputTable2
SELECT yourtable.*
FROM yourtable
WHERE Name IS NULL and Mobile IS NOT NULL

INSERT INTO OutputTable3
SELECT yourtable.*
FROM yourtable
WHERE Name IS NOT NULL and Mobile IS NULL

INSERT INTO OutputTable4
SELECT yourtable.*
FROM yourtable
WHERE Name IS NULL and Mobile IS NULL

关于mysql - 根据sqlserver/ssis中的非空条件将表拆分为多个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15907350/