解决方案 »
- 一个比较别扭的查询大家来帮忙啊!
- insert 2 select recordid
- 求修改方案:关于允许在盘点期间进行收发动作的改造的两点疑问!
- 如何写这条SQL语句,请各位指教,表示感谢了!!!
- 为什么在程序登陆界面中输入用户名和密码时,点确定按钮时总是显示“无法获得连接句柄!"”
- 高分求教,在线等。统计数据问题
- 各位高手,请问如何同时更改数据库名以及更改数据库的物理文件名?
- SQL复制问题?
- 怎么返回中间行
- 请问如何删除一个表的第N行?难道SQL没提供按指定行数删除的办法吗,我找了半天没找到
- SQL2000高并发下select取值重复的问题,希望有大神给予指导
- sqlserver2008中无索引无主键的临时表查询结果排序问题
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-10 16:45:59
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[Bak]
if object_id('[Bak]') is not null drop table [Bak]
go
create table [Bak]([id] int,[name] nvarchar(4))
insert [Bak]
select 1000,'N1' union all
select 1001,'N2' union all
select 1002,'N3'
--> 测试数据[Current]
if object_id('[Current]') is not null drop table [Current]
go
create table [Current]([id] int,[name] nvarchar(4))
insert [Current]
select 1000,'N1' union all
select 1000,'N9' union all
select 1001,'N2' union all
select 1002,'N3' union all
select 1002,'N4' union all
select 1002,'N5'
--------------生成数据--------------------------
SELECT * FROM [Current] WHERE id IN (
SELECT a.id
FROM (
select id,COUNT(1)[count] from [Current] GROUP BY id)a LEFT JOIN (
select id,COUNT(1)[count] from [Bak]GROUP BY id)b ON a.id=b.id AND a.[count]>b.[count]
WHERE b.id IS NOT NULL )
----------------结果----------------------------
/*
id name
----------- ----
1000 N1
1000 N9
1002 N3
1002 N4
1002 N5
*/
a.id
from
(select id,count(1) as num from bak group by id) as a
inner join
(select id,count(1) as num from Current group by id) as b
on
a.id=b.id
and
b.num>a.num
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-10 16:49:04
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[Bak]
if object_id('[Bak]') is not null drop table [Bak]
go
create table [Bak]([id] int,[name] varchar(2))
insert [Bak]
select 1000,'N1' union all
select 1001,'N2' union all
select 1002,'N3'
--> 测试数据:[Current]
if object_id('[Current]') is not null drop table [Current]
go
create table [Current]([id] int,[name] varchar(2))
insert [Current]
select 1000,'N1' union all
select 1000,'N9' union all
select 1001,'N2' union all
select 1002,'N3' union all
select 1002,'N4' union all
select 1002,'N5'
--------------开始查询--------------------------
select
a.id
from
(select id,count(1) as num from bak group by id) as a
inner join
(select id,count(1) as num from [Current] group by id) as b
on
a.id=b.id
and
b.num>a.num----------------结果----------------------------
/* id
-----------
1000
1002(2 行受影响)
*/