T-SQL问题

Question

我有下表（SQL Server）表名称是 LandParcels

Blockid   ParcelNo  Stateorprivate
========================
11001901   30       Deemana
11001901   35       Deemana
11001901   41       State
11001901   45       State
11001901   110      Private
11001901   111      Private

11001902   1        Deemana
11001902   11       State
11001902   16       Private

11002001   15       Deemana
11002001   16       State
11003001   20       Private
11002003   2        Deemana
11002003   3        State
11003003   4        Private

Blockid（数字）= 前 6 位数字用于地籍地图编号，后 2 位数字用于区块编号

，例如：110019 是地籍地图编号，01 是区块编号。

我使用了以下内容查询

select substring(ltrim(str(blockid)),1,6) as blockid,stateorprivate, count(*) as noofLP from LandParcels group by blockid, stateorprivate order by blockid asc

结果是

Blockid  Stateorprivate  noofLP
========================
110019   Deemana         2
110019   State           2
110019   Private         2
110019   Deemana         1
110019   State           1
110019   Private         1
110020   Deemana         1
110020   State           1
110020   Private         1
110020   Deemana         1
110020   State           1
110020   Private         1

我想获得报告的以下结果

blockid  noofBlocks   Deemana   State  Private  Amt_of_Deemana_State_Private
110019    2            3          3       3          9  
110020    2            2          2       2          6

如何查询。 请帮助我。

Answer 1

您开始查询：

select substring(ltrim(str(blockid)),1,6) as blockid

这立即给数据库带来了歧义——在查询的其余部分中，blockid 代表该名称的原始列，还是代表这个同名列？

不要这样做——让数据库引擎超载，甚至比它已经需要处理的模糊性还要多，这是荒谬的； 使用 as myblockid 或此处的任何内容，并在查询的其余部分中使用 myblockid（当 that 是您的意思时）。 这可能无法解决所有问题，但它将使您的生活、数据库引擎以及任何试图帮助您的人的生活不再是一场噩梦。

Answer 2

我不会检查这是否有效，但您应该考虑使用 sum 和 case。

select
substring(ltrim(str(blockid)),1,6) as blockid,
sum(case stateorprivate when 'Deemana' then 1 else 0 end) as Deemana,
sum(case stateorprivate when 'State' then 1 else 0 end) as State,
sum(case stateorprivate when 'Private' then 1 else 0 end) as Private,
count(*) as Amt_of_Deemana_State_Private
from LandParcels group by blockid 
order by blockid asc

Answer 3

您可以这样做：

SELECT 
    SUBSTRING(LTRIM(STR(Blockid)), 1, 6) AS blockid,
    COUNT(DISTINCT SUBSTRING(LTRIM(STR(Blockid)), 7, 2)) AS noofBlocks,
    SUM(CASE Stateorprivate WHEN 'Deemana' THEN 1 ELSE 0 END) AS Deemana,
    SUM(CASE Stateorprivate WHEN 'State' THEN 1 ELSE 0 END) AS [State],
    SUM(CASE Stateorprivate WHEN 'Private' THEN 1 ELSE 0 END) AS [Private],
    SUM(CASE Stateorprivate
        WHEN 'Deemana' THEN 1
        WHEN 'State' THEN 1
        WHEN 'Private' THEN 1
        ELSE 0
    END) AS Amt_of_Deemana_State_Private
FROM LandParcels
GROUP BY SUBSTRING(LTRIM(STR(Blockid)), 1, 6)

但是，如果数据库模式在您的控制之下，您应该考虑规范化。

Answer 4

像这样的东西吗？

SELECT substring(ltrim(str(lp.blockid)),1,6) as blockid, 
    w.noofBlocks
    x.Deemana,
    y.State,
    z.Private,
    COUNT(*) AS Amt_of_Deemana_State_Private
FROM LandParcels lp
    INNER JOIN (
        SELECT substring(ltrim(str(lp.blockid)),1,6) as myblockid, COUNT(*) AS Deemana
        FROM LandParcels lp2
        WHERE Stateorprivate = 'Deemana'
    ) x ON (substring(ltrim(str(lp.blockid)),1,6) = x.myblockid)
    INNER JOIN (
        SELECT substring(ltrim(str(lp.blockid)),1,6) as myblockid, COUNT(*) AS State
        FROM LandParcels lp3
        WHERE Stateorprivate = 'State'
    ) y ON (substring(ltrim(str(lp.blockid)),1,6) = y.myblockid)
    INNER JOIN (
        SELECT substring(ltrim(str(lp.blockid)),1,6) as myblockid, COUNT(*) AS Private
        FROM LandParcels lp4
        WHERE Stateorprivate = 'Private'
    ) z ON (substring(ltrim(str(lp.blockid)),1,6) = z.myblockid)
    CROSS JOIN (
        SELECT COUNT(DISTINCT substring(ltrim(str(lp.blockid)),1,6) as myblockid)
        FROM LandParcels lp5
    ) w
GROUP BY substring(ltrim(str(lp.blockid)),1,6)

Answer 5

我相信这个查询会达到您想要的结果，只不过 NoOfBlocks 字段是第一列而不是第二列。 根据 Alex Martelli 的建议，我还使用 CadastalMapNo 作为结果集列名称，而不是 blockid，因为它增加了歧义，因为已经有其他名称为 blockid。 我之所以将NoOfBlocks字段放在第一列，是因为我相信当使用distinct关键字时，SQLServer要求count函数作为选择列表中的第一个字段。

我还没有实际测试过这个，它的性能可能很差，但我很确定它是正确的，因为我理解这个问题。

    SELECT
        COUNT(DISTINCT SUBSTRING(LTRIM(STR(blockid)),7,8)) as NoOfBlocks,
        SUBSTRING(LTRIM(STR(blockid)),1,6) as CadastalMapNo, 
        (CASE WHEN Stateorprivate='Deemana' then 1 else 0 end) as Deemana,
        (CASE WHEN Stateorprivate='State' then 1 else 0 end) as State,
        (CASE WHEN Stateorprivate='Private' then 1 else 0 end) as Private,
        COUNT(*) as Amt_of_Deemana_State_Private 
    FROM 
        LandParcels
    GROUP BY 
        CadastalMapNo
    ORDER BY
        CadastalMapNo