Question

我有这个T-SQL(简化):

select 3.00 as score1, null as score2, null as score3, null as score4
union all
select null as score1, 4.5 as score2, 1.5 as score3, null as score4

生产:

score1  score2  score3  score4
------------------------------
3.00    NULL    NULL    NULL
NULL    4.5     1.5     NULL

但是,我还是要加入其中一行:

score1  score2  score3  score4
------------------------------
3.00    4.5     1.5     NULL

Sorry - impping blank(it晚)。

是否需要临时表格?

这样做的原因是,我有以下联系:

Reviews 1.* Scores

因此,如果你知道:

INSERT INTO OtherTable (ReviewId, Score1, Score2, Score3, Score4)
????

希望是有意义的。

http://www.ohchr.org。

基于@OMG 回答(他刚刚去除),即:

SELECT CASE MAX(x.score1) WHEN 0 THEN NULL ELSE MAX(x.score1) END AS score4
       CASE MAX(x.score2) WHEN 0 THEN NULL ELSE MAX(x.score2) END AS score4
       CASE MAX(x.score3) WHEN 0 THEN NULL ELSE MAX(x.score3) END AS score4
       CASE MAX(x.score4) WHEN 0 THEN NULL ELSE MAX(x.score4) END AS score4
  FROM (select 3.00 as score1, 0 as score2, 0 as score3, 0 as score4
        union all
        select 0 as score1, 4.5 as score2, 1.5 as score3, 0 as score4) x

但这只是粗略的。任何其他想法?

Answer 1

结束了我原来的问询(把我放在正确轨道上的要道:

SELECT CASE MAX(x.score1) WHEN 0 THEN NULL ELSE MAX(x.score1) END AS score4
       CASE MAX(x.score2) WHEN 0 THEN NULL ELSE MAX(x.score2) END AS score4
       CASE MAX(x.score3) WHEN 0 THEN NULL ELSE MAX(x.score3) END AS score4
       CASE MAX(x.score4) WHEN 0 THEN NULL ELSE MAX(x.score4) END AS score4
  FROM (select 3.00 as score1, 0 as score2, 0 as score3, 0 as score4
        union all
        select 0 as score1, 4.5 as score2, 1.5 as score3, 0 as score4) x

Answer 2

假设只有两行之一的得分1,分2,...... 否则,将使用<代码>NULIF功能重写你的询问。

SELECT
    NULLIF(ISNULL(NULLIF(x.score1, 0), y.score1), 0) score1,
    NULLIF(ISNULL(NULLIF(x.score2, 0), y.score2), 0) score2,
    NULLIF(ISNULL(NULLIF(x.score3, 0), y.score3), 0) score3,
    NULLIF(ISNULL(NULLIF(x.score4, 0), y.score4), 0) score4
FROM (SELECT 3.00 AS score1, 0 AS score2, 0 AS score3, 0 AS score4) x
CROSS JOIN (SELECT 0 AS score1, 4.5 AS score2, 1.5 AS score3, 0 AS score4) y

Answer 3

@OMG Ponies/RPM1984 querys:

SELECT MAX(x.score1),
       MAX(x.score2),
       MAX(x.score3),
       MAX(x.score4)
  FROM (select 3.00 as score1, null as score2, null as score3, CAST(null as int) as score4
        union all
        select null as score1, 4.5 as score2, 1.5 as score3, null as score4) x

其结果如下:

3.00    4.5 1.5 NULL

如果多行确定相同分数,我不肯定你做些什么。

我不得不在第四栏中增加CAST(null as int),因为否则,Serk无法找到第四栏的类型——全部为两栏,任何类型的无效。

Answer 4

在我尝试时(SQL2008年),使用一个简单的<条码>:。

SELECT 
    MAX(score1),
    MAX(score2),
    MAX(score3),
    MAX(score4)
FROM
    (select 3.00 as score1, null as score2, null as score3, null as score4 
    union all 
    select null as score1, 4.5 as score2, 1.5 as score3, null as score4) s

我接受这一考验。

declare @t table (i int null)
insert @t values (null)
insert @t values (1)
select MAX(i) from @t

这是你想要做的吗?

友情链接