select case when a.id=b.id then b.cieyName else '' end as cityName,A.roadCount
from tb a
left join
(select cityID,min(id) as id from tb group by cityID)b
on a.cityID=B.cityID
from tb a
left join
(select cityID,min(id) as id from tb group by cityID)b
on a.cityID=B.cityID
解决方案 »
- 求版主给个dateadd()使用案例
- 1000万用户需要在月初进行扣费操作,怎样对数据库操作!!!!在线等
- 如declare @dbname varchar(50) set @dbname='dbname', 请问C#中如何使用变量@dbname。
- 为什么JOIN 不会剔除重复记录
- 请问如何使用Begin Transaction这类T-SQL!
- 数据库倒数据的问题
- 搭配win03r2x64系统用sql2005好还是sql2008好?
- 请教各位大师 SQL2K 4IN1装哪个好点 个人/标准/开发版???
- 在SQL6。5中怎样给记录解锁?
- 怪怪怪!在C++ Builder5中独独执行这条SQL语句出错,大家看看...
- 如何防止某个插入重复的纪录?
- SQL难题。。。〔3〕
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-08 14:31:29
---------------------------------
--> 生成测试数据表-aif not object_id('a') is null
drop table a
Go
Create table a([ID] int,[cityName] nvarchar(2))
Insert a
select 1,'北京' union all
select 2,'上海' union all
select 3,'深圳' union all
select 4,'广州'
Go--> 生成测试数据表-bif not object_id('b') is null
drop table b
Go
Create table b([ID] int,[cityID] int,[roadCount] int)
Insert b
select 1,1,100 union all
select 2,1,121 union all
select 3,1,14 union all
select 4,2,13 union all
select 5,2,141 union all
select 6,3,114 union all
select 7,3,165 union all
select 8,3,15 union all
select 9,4,199
Go-->SQL查询如下:
select cityname=case when not exists(select 1 from b t where t.cityid=b.cityid and t.id<b.id) then a.cityname else '' end,b.roadcount
from a
join b
on a.id=b.cityid
/*
cityname roadcount
-------- -----------
北京 100
121
14
上海 13
141
深圳 114
165
15
广州 199(9 行受影响)*/
Insert a
select 1,'北京' union all
select 2,'上海' union all
select 3,'深圳' union all
select 4,'广州'Create table b([ID] int,[cityID] int,[roadCount] int)
Insert b
select 1,1,100 union all
select 2,1,121 union all
select 3,1,14 union all
select 4,2,13 union all
select 5,2,141 union all
select 6,3,114 union all
select 7,3,165 union all
select 8,3,15 union all
select 9,4,199select case when a.id=t.cityid then a.cityName else '' end as cityName,b.roadCount
from b
left join
(select cityID,min(id) as id from b group by cityID)t
on b.id=t.id and b.cityid=t.cityid
left join a on a.id=b.cityIDcityName roadcount
----------------------
北京 100
121
14
上海 13
141
深圳 114
165
15
广州 199