所以基本上有1个问题和1个问题：
1.问题 - 当我在表中有100列时（并且没有设置键或uindex），并且我想要自己连接或重新选择该表时，是否真的必须写出每个列名？
2.问题 - 下面的例子显示了1.问题和我的实际SQL语句问题
例：
A.FIELD1, (SELECT CASE WHEN B.FIELD2 = 1 THEN B.FIELD3 ELSE null FROM TABLE B WHERE A.* = B.*) AS CASEFIELD1 (SELECT CASE WHEN B.FIELD2 = 2 THEN B.FIELD4 ELSE null FROM TABLE B WHERE A.* = B.*) AS CASEFIELD2 FROM TABLE A GROUP BY A.FIELD1故事是：如果我没有把CASE放到它自己的select语句中，那么我必须将实际的rowname放到GROUP BY中，并且GROUP BY不会将CASE的NULL值分组，而是将来自行。 正因为如此，我不得不加入所有专栏或对所有专栏进行再选择，因为没有密钥，也没有uindex，或者以某种方式找到另一种解决方案。
DBServer是DB2。
所以现在只需要用文字和SQL来描述它：我有可以分为“ZD”和“EK”（1 = ZD，2 = EK）的“订购商品”，并且可以按“分销商”进行分组。 尽管“订单项目”可以具有两个不同的“部门”（ZD，EK）中的一个，但“ZD”和“EK”的字段/行始终都会填充。 我需要分组来考虑“部门”，只有当指定的“部门”（ZD或EK）发生变化时，我才需要创建一个新组。
SELECT (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END) AS ZD, (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END) AS EK, TABLE.DISTRIBUTOR, sum(TABLE.SOMETHING) AS SOMETHING, FROM TABLE GROUP BY ZD EK TABLE.DISTRIBUTOR TABLE.DEPARTEMENT这里在GROUP BY中的SELECT和ZD，EK中工作。 唯一的问题是，即使EK不是指定的DEPARTEMENT，如果它改变了，它仍然会打开一个新的组，因为他使用的是真实的EK值，而不是CASE中的NULL，因为我已经在解释顶部。
so basicially there is 1 question and 1 problem:
1. question - when I have like 100 columns in a table(and no key or uindex is set) and I want to join or subselect that table with itself, do I really have to write out every column name?
2. problem - the example below shows the 1. question and my actual SQL-statement problem
Example:
A.FIELD1, (SELECT CASE WHEN B.FIELD2 = 1 THEN B.FIELD3 ELSE null FROM TABLE B WHERE A.* = B.*) AS CASEFIELD1 (SELECT CASE WHEN B.FIELD2 = 2 THEN B.FIELD4 ELSE null FROM TABLE B WHERE A.* = B.*) AS CASEFIELD2 FROM TABLE A GROUP BY A.FIELD1The story is: if I don't put the CASE into its own select statement then I have to put the actual rowname into the GROUP BY and the GROUP BY doesn't group the NULL-value from the CASE but the actual value from the row. And because of that I would have to either join or subselect with all columns, since there is no key and no uindex, or somehow find another solution.
DBServer is DB2.
So now to describing it just with words and no SQL: I have "order items" which can be divided into "ZD" and "EK" (1 = ZD, 2 = EK) and can be grouped by "distributor". Even though "order items" can have one of two different "departements"(ZD, EK), the fields/rows for "ZD" and "EK" are always both filled. I need the grouping to consider the "departement" and only if the designated "departement" (ZD or EK) is changing, then I want a new group to be created.
SELECT (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END) AS ZD, (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END) AS EK, TABLE.DISTRIBUTOR, sum(TABLE.SOMETHING) AS SOMETHING, FROM TABLE GROUP BY ZD EK TABLE.DISTRIBUTOR TABLE.DEPARTEMENTThis here worked in the SELECT and ZD, EK in the GROUP BY. Only problem was, even if EK was not the designated DEPARTEMENT, it still opened a new group if it changed, because he was using the real EK value and not the NULL from the CASE, as I was already explaining up top.
最满意答案
而女士们，先生们，这是解决问题的方法：
SELECT (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END) AS ZD, (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END) AS EK, TABLE.DISTRIBUTOR, sum(TABLE.SOMETHING) AS SOMETHING, FROM TABLE GROUP BY (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END), (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END), TABLE.DISTRIBUTOR, TABLE.DEPARTEMENT@ t-clausen.dk：谢谢！
@其他： ...
And here ladies and gentleman is the solution to the problem:
SELECT (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END) AS ZD, (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END) AS EK, TABLE.DISTRIBUTOR, sum(TABLE.SOMETHING) AS SOMETHING, FROM TABLE GROUP BY (CASE WHEN TABLE.DEPARTEMENT = 1 THEN TABLE.ZD ELSE null END), (CASE WHEN TABLE.DEPARTEMENT = 2 THEN TABLE.EK ELSE null END), TABLE.DISTRIBUTOR, TABLE.DEPARTEMENT@t-clausen.dk: Thank you!
@others: ...
更多推荐
发布评论