如何根据sql server中行的列值从表中选择行

debugcn 发表于 Dev

拉利特夏尔马

我想根据 sql server 中的列值选择行，请检查下面的示例并获得所需的结果。

 WITH allData
AS (
select mlid=1,value=0,checkid=1
union all
select mlid=2,value=6,checkid=2
union all
select mlid=3,value=6,checkid=1
union all
select mlid=4,value=0,checkid=2
)
select * from allData

结果

   Mlid  Value checked
    1    0      1
    2    6      2
    3    6      1
    4    0      2

所需结果 -->
条件：- 如果选中的列值为 1 且值列为 0 则显示选中的值 2 行仅
显示选中的列值 1 如下结果

Mlid value checked   
2     6      2
3     6      1

理查德·汉塞尔

这将适用于您的示例数据，但几乎无法用于其他任何事情？

WITH allData AS (
    SELECT MLID = 1, [VALUE] = 0, CHECKID = 1
    UNION ALL
    SELECT MLID = 2, [VALUE] = 6, CHECKID = 2
    UNION ALL
    SELECT MLID = 3, [VALUE] = 6, CHECKID = 1
    UNION ALL
    SELECT MLID = 4, [VALUE] = 0, CHECKID = 2)
SELECT 
    CASE WHEN a1.CHECKID = 1 AND a1.VALUE = 0 THEN a2.MLID ELSE a1.MLID END AS MLID,
    CASE WHEN a1.CHECKID = 1 AND a1.VALUE = 0 THEN a2.[VALUE] ELSE a1.[VALUE] END AS [VALUE],
    CASE WHEN a1.CHECKID = 1 AND a1.VALUE = 0 THEN a2.CHECKID ELSE a1.CHECKID END AS CHECKID
FROM 
    allData a1
    INNER JOIN allData a2 ON a2.MLID = a1.MLID + 1 AND a2.CHECKID = 2
WHERE
    a1.CHECKID = 1;

我想这可能会让您开始进行更好的查询，甚至提出一些关于您实际需要什么以及这些行如何相关的问题？

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。