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

块No (数字)=用于地籍地图编号的前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,
    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 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

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

不会这样做 --重载一个DB引擎而使其处理的歧义更多是荒谬的；在这里使用as myblockid或其他任何东西，在查询的其余部分中使用myblockid，当就是您想要的时候。这可能不能解决所有问题，但它将使您的生活、DB引擎以及任何试图帮助您解决问题的人的生活不再是噩梦。