select * from a where not exists(select 1 from where name=a.name and pic=a.pic and code<a.code)
解决方案 »
- 在sql中,3张表连接查询
- 这个储存过程怎么写呀???????? 急求解决!!~
- 请问一下能不能将用Visio建的数据库模型到入SQL中?
- 请教win2000怎么连接win2000
- SQL中,如何判断某个字段是否包含中文(简体和繁体)
- 算经典问题吧?机器名字改了,sqlserver不动了
- 我在恢复一个数据库时出现一个问题,提示是这样的'操作系统不支持代码页970。RESTORE DATABASE操作异常终止’,请问是什么问题,如何解决
- 如何提取这样的字符
- 一个储存过程中,一个复杂的语句和一个实现同样功能的多个分开的语句,哪个更高效率些呢.
- 如何检查Sql Server数据库无用的用户和登录名
- 我想做一个IP访问统计,如果导入Msmsql数据库,并分三列, 我试过导入只有一列?
- 定位记录的问题,如何使用fetch?
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
drop table
Go
Create table ([code] int,[name] nvarchar(5),[pic] int)
Insert
select 1,N'cpu',256 union all
select 2,N'cpu',256 union all
select 3,N'cpu',233 union all
select 4,N'model',53 union all
select 5,N'model',53 union all
select 6,N'power',123
Go
select * from a where not exists(select 1 from where name=a.name and pic=a.pic and code<a.code)code name pic
----------- ----- -----------
1 cpu 256
3 cpu 233
4 model 53
6 power 123(4 個資料列受到影響)
code name pic
----------- ----- -----------
1 cpu 256
3 cpu 233
只要这样的价格变动数据就可以了~~
再帮一下忙,好吗?roy_88大哥.
drop table
Go
Create table ([code] int,[name] nvarchar(5),[pic] int)
Insert
select 1,N'cpu',256 union all
select 2,N'cpu',256 union all
select 3,N'cpu',233 union all
select 4,N'model',53 union all
select 5,N'model',53 union all
select 6,N'power',123select
min([code])[code],[name],[pic]
from
a
where
(select count(distinct [pic]) from where [name]=a.[name])>1
group by [name],[pic]
order by [code]code name pic
----------- ----- -----------
1 cpu 256
3 cpu 233
--类似于重复记录,保存一条
select * from a
where not exists(select 1 from b
where a.name=b.name and a.pic=b.pic and a.code>b.code)
--借数据
if not object_id('') is null
drop table
Go
Create table ([code] int,[name] nvarchar(5),[pic] int)
Insert
select 1,N'cpu',256 union all
select 2,N'cpu',256 union all
select 3,N'cpu',233 union all
select 4,N'model',53 union all
select 5,N'model',53 union all
select 6,N'power',123
select * from a
where not exists(select 1 from b
where a.name=b.name and a.pic=b.pic and a.code>b.code)/*
code name pic
----------- ----- -----------
1 cpu 256
3 cpu 233
4 model 53
6 power 123(4 行受影响)
*/
where not exists(select 1 from a where a.name=b.name and a.pic=b.pic and a.code<b.code)
and exists (select 1 from (select name,count(name) as sl from (select distinct name,pic from ) as c group by c.name) as d where d.name=b.name and d.sl>1)
--@是商品表,code是ID,name是商品名,pic是价格
declare @ table(
code nvarchar(20),
name nvarchar(20),
pic int
)insert into @
select '1', '1', 1 union
select '2', '1', 1 union
select '3', '1', 2 union
select '4', '2', 1 union
select '5', '2', 1 union
select '7', '3', 1 union
select '8', '1', 3 union
select '9', '1', 3 union
select '10','1', 1 union
select '11','2', 2 union
select '12','3', 2 --我要查询的数据是最新的价格与最近的价格比较(注:就是同一种商品名第2条价格与第1条价格比,第3条价格与第2条价格比...),有变化的就取出记录.
--对于上述的记录集,我想要的数据是:
code name pic
'3', '1', 2
'8', '1', 3
'10','1', 1
'11','2', 2
'12','3', 2