改为
SELECT *
FROM AirInformation as t1
WHERE EXISTS
(SELECT hawb, COUNT(*) AS num
FROM airinformation AS t2
where t1.hawb = t2.hawb --加入
GROUP BY hawb
HAVING COUNT(*) > 1)
SELECT *
FROM AirInformation as t1
WHERE EXISTS
(SELECT hawb, COUNT(*) AS num
FROM airinformation AS t2
where t1.hawb = t2.hawb --加入
GROUP BY hawb
HAVING COUNT(*) > 1)
解决方案 »
- SQL 查询语句问题 写了好久还是不行
- 纯新手,写个触发器,变量定义报错
- 请教有关根据判断条件进行SQL语句拼接的问题
- 请教句SQL
- 这个 Sql可以用 DISTINCT实现吗
- 请问哪位兄弟在XP上装过SQLSERVER2000个人版,怎么我装完后企业管理器不能使用,别的倒是都可以
- 寻求一对多的较好解决方案,高手帮忙
- 每隔5行一汇总
- 请问 Dao 中 SELECT 日期时间的查询怎么写?
- 在一个表中,怎样取出随机的几条记录?
- 关于临时表的疑问,请邹建等高手进来讨论一下
- SQL Server 2000 在企业管理器中创建全文索引,可在“工具”菜单上“全文索引”是灰色的,不能用啊~~哪们兄弟知道是怎么回事啊??
FROM AirInformation as t1
WHERE EXISTS
(SELECT hawb, COUNT(*) AS num
FROM airinformation AS t2
GROUP BY hawb
HAVING COUNT(*) > 1)!=
select * from airinformation
where hawb in (SELECT hawb, COUNT(*) AS num
FROM airinformation AS t2
GROUP BY hawb
HAVING COUNT(*) > 1) a
SELECT *
FROM AirInformation as t1
WHERE EXISTS
(SELECT * FROM airinformation Where hawb=t1.hawb HAVING COUNT(*) > 1)
一句惊醒梦中人,可怜我的66分啊:)
谢谢了.
libin_ftsafe(子陌红尘) :
IN 只能跟踪一个字段,hawb,count(*)算两个字段了,会包错了:)
--------------------------------------------------------
SELECT
*
FROM
AirInformation as t1
WHERE
EXISTS
(SELECT
hawb, COUNT(*) AS num
FROM
airinformation AS t2
GROUP BY
hawb
HAVING COUNT(*) > 1)
楼主用视图实现的查询语句可以近似于下面的语句:
--------------------------------------------------------
SELECT
a.*
FROM
AirInformation as a
WHERE
a.hawb in
(SELECT
hawb
FROM
airinformation
where
hawb = a.hawb
GROUP BY
hawb
HAVING COUNT(*) > 1)正确且优化过的语句如下:
--------------------------------------------------------
SELECT
a.*
FROM
AirInformation as a
WHERE
EXISTS
(SELECT
1
FROM
airinformation
where
hawb = a.hawb
GROUP BY
hawb
HAVING COUNT(*) > 1)
如果我想删除hawb重复的记录,并保留重复hawb中的一条,如何去做?
Delete A from AirInformation A Where Exists(Select * from AirInformation Where hawb =A.hawb And ID<A.ID))没有ID字段Alter Table AirInformation Add ID Int Identity(1,1)
Delete A from AirInformation A Where Exists(Select * from AirInformation Where hawb =A.hawb And ID<A.ID))
Alter Table AirInformation Drop Column ID