Подсчет на основе строки и нулевой

Похоже, основная путаница вызвана оператором SQL в начале вопроса, где вы просто GROUP BY LASTNAME, HOUSENO .

Если вам нужна простая группировка, ваш запрос будет правильным. Но затем вы показываете нам более подробные данные образца с ожидаемым результатом, и становится ясно, что вы хотите не просто группировать (который не заботится о порядке строк в данных), но вы хотите группировать строки на основе их последовательность.

Это классическая проблема, называемая gaps-and-islands . В SQL Server 2008 это можно сделать, используя несколько вызовов функции ROW_NUMBER .

Пример данных

 DECLARE @T TABLE (id int PRIMARY KEY ,FirstName nvarchar(50) ,LastName nvarchar(50) ,HouseNo nvarchar(50) ,MyCount int ,CountId int); INSERT INTO @T (id, FirstName, LastName, HouseNo) VALUES (1 , 'Imran ', 'Khan ', '1-1'), (2 , 'Waseem', 'Khan ', '1-1'), (3 , 'Rihan ', 'Khan ', '1-1'), (4 , 'Moiz ', 'Shaikh', '1-2'), (5 , 'Zbair ', 'Shaikh', '1-2'), (6 , 'Sultan', 'Shaikh', '1-2'), (7 , 'Zaid ', 'Khan ', NULL), (10, 'Parvez', 'Patel ', '1-3'), (11, 'Ahmed ', 'Patel ', '1-3'), (12, 'Rahat ', 'Syed ', '1-4'), (13, 'Talha ', 'Khan ', NULL), (14, 'Zia ', 'Khan ', NULL), (15, 'Arshad', 'Patel ', '1-3'), (16, 'Samad ', 'Patel ', '1-3'), (17, 'Raees ', 'Syed ', '1-4'), (18, 'Azmat ', 'Khan ', NULL), (19, 'Imran ', 'Khan ', NULL); 

Запрос SELECT

 WITH CTE_RN AS ( SELECT id ,FirstName ,LastName ,HouseNo ,MyCount ,CountId ,ROW_NUMBER() OVER (PARTITION BY LastName, HouseNo ORDER BY ID) AS rn1 ,ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM @T AS T ) ,CTE_GRoups AS ( SELECT id ,FirstName ,LastName ,HouseNo ,MyCount ,CountId ,rn1 ,rn2 ,rn2-rn1 AS GroupNumber ,COUNT(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS NewMyCount ,MIN(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS GroupMinID FROM CTE_RN ) SELECT id ,FirstName ,LastName ,HouseNo ,rn1 ,rn2 ,GroupNumber ,NewMyCount ,GroupMinID ,DENSE_RANK() OVER (ORDER BY GroupMinID) AS NewCountId FROM CTE_GRoups ORDER BY ID; 

результат

 +----+-----------+----------+---------+-----+-----+-------------+------------+------------+------------+ | id | FirstName | LastName | HouseNo | rn1 | rn2 | GroupNumber | NewMyCount | GroupMinID | NewCountId | +----+-----------+----------+---------+-----+-----+-------------+------------+------------+------------+ | 1 | Imran | Khan | 1-1 | 1 | 1 | 0 | 3 | 1 | 1 | | 2 | Waseem | Khan | 1-1 | 2 | 2 | 0 | 3 | 1 | 1 | | 3 | Rihan | Khan | 1-1 | 3 | 3 | 0 | 3 | 1 | 1 | | 4 | Moiz | Shaikh | 1-2 | 1 | 4 | 3 | 3 | 4 | 2 | | 5 | Zbair | Shaikh | 1-2 | 2 | 5 | 3 | 3 | 4 | 2 | | 6 | Sultan | Shaikh | 1-2 | 3 | 6 | 3 | 3 | 4 | 2 | | 7 | Zaid | Khan | NULL | 1 | 7 | 6 | 1 | 7 | 3 | | 10 | Parvez | Patel | 1-3 | 1 | 8 | 7 | 2 | 10 | 4 | | 11 | Ahmed | Patel | 1-3 | 2 | 9 | 7 | 2 | 10 | 4 | | 12 | Rahat | Syed | 1-4 | 1 | 10 | 9 | 1 | 12 | 5 | | 13 | Talha | Khan | NULL | 2 | 11 | 9 | 2 | 13 | 6 | | 14 | Zia | Khan | NULL | 3 | 12 | 9 | 2 | 13 | 6 | | 15 | Arshad | Patel | 1-3 | 3 | 13 | 10 | 2 | 15 | 7 | | 16 | Samad | Patel | 1-3 | 4 | 14 | 10 | 2 | 15 | 7 | | 17 | Raees | Syed | 1-4 | 2 | 15 | 13 | 1 | 17 | 8 | | 18 | Azmat | Khan | NULL | 4 | 16 | 12 | 2 | 18 | 9 | | 19 | Imran | Khan | NULL | 5 | 17 | 12 | 2 | 18 | 9 | +----+-----------+----------+---------+-----+-----+-------------+------------+------------+------------+ 

Здесь я включил в результат все промежуточные шаги, чтобы вы могли видеть, как это работает. Основная часть – два набора ROW_NUMBER . Последовательность rn1 перезапускается для каждого LastName, HouseNo . Он разделен на LastName, HouseNo . rn2 – простая возрастающая последовательность без пробелов. Нам это нужно, потому что исходный ID определяет порядок, но может иметь пробелы.

Затем мы вычитаем эти две последовательности и разность дает нам GroupNumber .

Подсчет количества элементов в группе является простым COUNT , что дает нам NewMyCount .

Перечисление групп с последовательными номерами без пробелов выполняется в два этапа. Сначала MIN дает идентификатор для группы, затем DENSE_RANK генерирует последовательность NewCountId без пробелов.

Если вы хотите фактически обновить исходную таблицу с помощью рассчитанного NewMyCount и NewCountId , легко включить запрос SELECT выше в запрос UPDATE :

Запрос UPDATE

 WITH CTE_RN AS ( SELECT id ,FirstName ,LastName ,HouseNo ,MyCount ,CountId ,ROW_NUMBER() OVER (PARTITION BY LastName, HouseNo ORDER BY ID) AS rn1 ,ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM @T AS T ) ,CTE_GRoups AS ( SELECT id ,FirstName ,LastName ,HouseNo ,MyCount ,CountId ,rn1 ,rn2 ,rn2-rn1 AS GroupNumber ,COUNT(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS NewMyCount ,MIN(ID) OVER (PARTITION BY LastName, HouseNo, rn2-rn1) AS GroupMinID FROM CTE_RN ) ,CTE_Update AS ( SELECT id ,FirstName ,LastName ,HouseNo ,MyCount ,CountId ,rn1 ,rn2 ,GroupNumber ,NewMyCount ,GroupMinID ,DENSE_RANK() OVER (ORDER BY GroupMinID) AS NewCountId FROM CTE_GRoups ) UPDATE CTE_Update SET MyCount = NewMyCount ,CountId = NewCountId ; 

результат

 SELECT * FROM @T ORDER BY ID; +----+-----------+----------+---------+---------+---------+ | id | FirstName | LastName | HouseNo | MyCount | CountId | +----+-----------+----------+---------+---------+---------+ | 1 | Imran | Khan | 1-1 | 3 | 1 | | 2 | Waseem | Khan | 1-1 | 3 | 1 | | 3 | Rihan | Khan | 1-1 | 3 | 1 | | 4 | Moiz | Shaikh | 1-2 | 3 | 2 | | 5 | Zbair | Shaikh | 1-2 | 3 | 2 | | 6 | Sultan | Shaikh | 1-2 | 3 | 2 | | 7 | Zaid | Khan | NULL | 1 | 3 | | 10 | Parvez | Patel | 1-3 | 2 | 4 | | 11 | Ahmed | Patel | 1-3 | 2 | 4 | | 12 | Rahat | Syed | 1-4 | 1 | 5 | | 13 | Talha | Khan | NULL | 2 | 6 | | 14 | Zia | Khan | NULL | 2 | 6 | | 15 | Arshad | Patel | 1-3 | 2 | 7 | | 16 | Samad | Patel | 1-3 | 2 | 7 | | 17 | Raees | Syed | 1-4 | 1 | 8 | | 18 | Azmat | Khan | NULL | 2 | 9 | | 19 | Imran | Khan | NULL | 2 | 9 | +----+-----------+----------+---------+---------+---------+ 

Используйте CTE, а затем обновите таблицу следующим образом:

 ;WITH T AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS SrNo, ROW_NUMBER() OVER (PARTITION BY LastName,HouseNo ORDER BY HouseNo) AS PartNo FROM MYTABLE ), X as ( SELECT T.LastName, T.HouseNo, (MAX(T.ID)-MIN(T.ID))+1 AS NoOfCount, ROW_NUMBER() OVER(Order BY MAX(ID)) AS RowNo, MAX(ID) AS ID FROM T GROUP BY T.LastName,T.HouseNo, (T.SrNo - T.PartNo) ) Update MYTABLE SET MyCount=X.NoOfCount, CountId=X.RowNo FROM X WHERE MYTABLE.LastName=X.LastName AND MYTABLE.HouseNo=X.HouseNo AND MYTABLE.ID<=X.ID SELECT * FROM MYTABLE 

Вывод:

Это должно сделать это

 declare @temp table (id int, firstname varchar(5), lastname varchar(5), houseno varchar(5), mycount int) insert into @temp values(1, 'A', 'C', '1-1', 2) insert into @temp values(2, 'B', 'C', '1-1', 2) insert into @temp values(4, 'D', 'A', null, 3) insert into @temp values(5, 'F', 'A', null, 3) insert into @temp values(6, 'J', 'A', null, 3) insert into @temp values(7, 'Q', 'X', '1-2', 3) insert into @temp values(8, 'D', 'X', '1-2', 3) insert into @temp values(9, 'D', 'X', '1-2', 3) insert into @temp values(10, 'A', 'C', '1-3', 3) insert into @temp values(11, 'B', 'C', '1-3', 3) insert into @temp values(12, 'C', 'C', '1-3', 3) insert into @temp values(14, 'F', 'K', null, 2) insert into @temp values(15, 'J', 'K', null, 2) insert into @temp values(16, 'Q', 'X', '1-5', 1) select count(ID) as _count from @temp group by isnull(lastname, ''), isnull(houseno, '') 

это возвращает

 _count 3 2 2 3 3 1 

Вы можете выплюнуть более подробную информацию:

 select distinct t.lastname, isnull(t.houseno, '') as houseno, (select count(ID) from @temp t2 where t2.lastname = t.lastname and t2.houseno = t.houseno) as _count_filled, (select count(ID) from @temp t2 where t2.lastname = t.lastname and isnull(t2.houseno, '') = isnull(t.houseno, '') and t2.houseno is null) as _count_empty from @temp t 

он вернет это:

 lastname houseno _count_filled _count_empty A 0 3 C 1-1 2 0 C 1-3 3 0 K 0 2 X 1-2 3 0 X 1-5 1 0 

Я считаю, что ваша 3-я, главная цель – обновить столбец MYCOUNT с результатами в соответствующих строках. В общем, вы ищете коррелированный подзапрос .

 UPDATE MYTABLE T1 SET T1.MYCOUNT = ( SELECT COUNT (*) FROM MYTABLE T2 WHERE T1.LASTNAME = B2.LASTNAME AND NVL (T2.HOUSENO, 0) = NVL (T1.HOUSENO, 0) GROUP BY T2.LASTNAME, T2.HOUSENO); 

* Примечание. Это реализовано для Oracle SQL

 SELECT COUNT(ID) AS _COUNT FROM MYTABLE GROUP BY ISNULL(LASTNAME, ''), ISNULL(HOUSENO, ''); 

Я согласен с анализом Владимира Баранова, поэтому я не буду здесь повторять. Я просто хочу сделать запрос немного проще, как следующее (протестировано в SQL Server 2012)

 --drop table #temp create table #temp (id int, firstname varchar(15), lastname varchar(15), houseno varchar(5)); go insert into #temp (id, firstname, lastname, houseno) values (1  , 'Imran'      ,'Khan'       ,'1-1')       ,(2  , 'Waseem'     ,'Khan'       ,'1-1')       ,(3  , 'Rihan'      ,'Khan'       ,'1-1')       ,(4  , 'Moiz'       ,'Shaikh'     ,'1-2')       ,(5  , 'Zbair'      ,'Shaikh'     ,'1-2')       ,(6  , 'Sultan'     ,'Shaikh'     ,'1-2')       ,(7  , 'Zaid'       ,'Khan'       , null)       ,(10 , 'Parvez'     ,'Patel'      ,'1-3')       ,(11 , 'Ahmed'      ,'Patel'      ,'1-3')       ,(12 , 'Rahat'      ,'Syed'       ,'1-4')       ,(13 , 'Talha'      ,'Khan'       ,null )       ,(14 , 'Zia'        ,'Khan'       ,null )       ,(15 , 'Arshad'     ,'Patel'      ,'1-3')       ,(16 , 'Samad'       ,'Patel'      ,'1-3')       ,(17 , 'Raees'      ,'Syed'       ,'1-4')       ,(18 , 'Azmat'      ,'Khan'       , null)    ,(19 , 'Imran'      ,'Khan'       , null) -- query ; with c as ( select id, firstname, lastname, houseno=isnull(houseno, '') , new_id=row_number() over (partition by lastname, isnull(houseno, '') order by id) , grp = id -row_number() over (partition by lastname, isnull(houseno, '') order by id) FROM #temp ) , d as ( select id, firstname, lastname, houseno, T.cnt, c.grp , row_id=id-row_number() over ( partition by grp, houseno order by c.grp) from c cross apply (select cnt=count(*) from c as c2 where c.grp = c2.grp and c.lastname=c2.lastname and c.houseno=c2.houseno) T(cnt) ) select id, FirstName, LastName, Houseno, MyCount=cnt, CountId= DENSE_RANK() over (order by row_id) from d 

Результат следующий:

Прежде всего создайте представление для подсчета количества и ранга каждой части.

 CREATE VIEW cnt AS SELECT T.LastName, T.HouseNo, MIN(t.id) AS START , MAX(T.id) AS finish , (MAX(T.ID)-MIN(T.ID))+1 AS NoOfCount, ROW_NUMBER() OVER(Order BY MAX(T.ID)) AS RowNo, MAX(T.ID) AS ID FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS SrNo, ROW_NUMBER() OVER (PARTITION BY LastName,HouseNo ORDER BY HouseNo) AS PartNo FROM myTable ) T GROUP BY T.LastName,T.HouseNo, (T.SrNo - T.PartNo) 

затем используйте его для своей цели:

 SELECT a.*, b.NoOfCount, b.RowNo FROM myTable AS a INNER JOIN cnt AS b ON a.id BETWEEN b.start AND b.finish 

и вот результат:

попробуй это:

SELECT COUNT (ID) AS _COUNT FROM MYTABLE GROUP ПО LASTNAME + ISNULL (HOUSENO, '')