解决方案 »
- SQL 2008 能不能增加禁止访问的IP
- SQL Server 2005 Service Pack 2怎样安装
- 一个分别删除存储过程,触发器,表的简单问题
- 昨天面试题目 求一个存储过程的实现-------------- 把查询语句当做输入参数
- 字符转换的问题
- 特殊字符问题!!!高手帮忙!!!
- 我的sql server登录不了,麻烦大家看看。
- -------------------------------------------------------难倒我的SQL查询问题
- 查询第xxx行数据,用什么命令?
- 在vb里用SQLDMO对象执行sql文件的例子(再线等待)
- 在几百个表中如何根据某一表名找到此表
- 请问怎样查询借用数量和图号
where not exists(select 1 from 表2 b where 用户卡号=b.用户卡号 and 消费时间>a.消费时间)
and b.消费时间 between @bdate and @edate
insert into t1 select '张三','10000001','360502198511050011'
insert into t1 select '张四','10000002','360502198511050012'
insert into t1 select '张六','10000004','360502198511050014'
insert into t1 select '张五','10000003','360502198511050013'
insert into t1 select '张七','10000005','360502198511050015'
create table tb(用户卡号 varchar(10),消费金额 int,消费时间 datetime)
insert into tb select '10000003',100,'2011-7-14 01:23:25'
insert into tb select '10000002',9,'2011-7-1 01:23:25'
insert into tb select '10000001',100,'2011-7-8 01:23:25'
insert into tb select '10000001',89,'2011-7-7 01:23:25'
insert into tb select '10000002',100,'2011-7-2 01:23:25'
insert into tb select '10000004',9,'2011-7-6 01:23:25'
insert into tb select '10000002',100,'2011-7-21 01:23:25'
insert into tb select '10000005',89,'2011-7-6 01:23:25'
insert into tb select '10000002',100,'2011-7-7 01:23:25'
insert into tb select '10000005',9,'2011-7-10 01:23:25'
insert into tb select '10000002',100,'2011-7-16 01:23:25'
insert into tb select '10000003',89,'2011-7-7 01:23:25'
insert into tb select '10000002',100,'2011-7-7 01:23:25'
insert into tb select '10000001',100,'2011-7-18 01:23:25'
insert into tb select '10000002',100,'2011-7-13 01:23:25'
insert into tb select '10000005',89,'2011-7-20 01:23:25'
go
select b.用户卡号,a.用户姓名,a.用户证件,b.消费金额,b.消费时间 into #
from t1 a inner join tb b on a.用户卡号=b.用户卡号
where 消费金额=100
select * from # a where not exists(select 1 from # where 用户姓名=a.用户姓名 and 消费时间<a.消费时间)
/*
用户卡号 用户姓名 用户证件 消费金额 消费时间
---------- ---------- ------------------------------ ----------- -----------------------
10000001 张三 360502198511050011 100 2011-07-08 01:23:25.000
10000002 张四 360502198511050012 100 2011-07-02 01:23:25.000
10000003 张五 360502198511050013 100 2011-07-14 01:23:25.000(3 行受影响)
*/
go
drop table t1,tb,#
where not exists(select 1 from 表2 b where 用户卡号=b.用户卡号 and 消费时间>a.消费时间)
and b.消费时间 between @bdate and @edate and b.消费金额=100
--> 测试数据: [用户信息表]
if object_id('[用户信息表]') is not null drop table [用户信息表]
create table [用户信息表] (用户姓名 varchar(4),用户卡号 int,用户证件 bigint)
insert into [用户信息表]
select '张八',10000006,360502198511050016 union all
select '张三',10000001,360502198511050011 union all
select '张四',10000002,360502198511050012 union all
select '张六',10000004,360502198511050014 union all
select '张五',10000003,360502198511050013 union all
select '张七',10000005,360502198511050015
--> 测试数据: [消费记录表]
if object_id('[消费记录表]') is not null drop table [消费记录表]
create table [消费记录表] (用户卡号 int,消费金额 int,消费时间 datetime)
insert into [消费记录表]
select 10000003,100,'2011-7-14 01:23:25' union all
select 10000002,9,'2011-7-1 01:23:25' union all
select 10000001,100,'2011-7-8 01:23:25' union all
select 10000001,89,'2011-7-7 01:23:25' union all
select 10000002,100,'2011-7-2 01:23:25' union all
select 10000004,9,'2011-7-6 01:23:25' union all
select 10000002,100,'2011-7-21 01:23:25' union all
select 10000005,89,'2011-7-6 01:23:25' union all
select 10000002,100,'2011-7-7 01:23:25' union all
select 10000005,9,'2011-7-10 01:23:25' union all
select 10000002,100,'2011-7-16 01:23:25' union all
select 10000003,89,'2011-7-7 01:23:25' union all
select 10000002,100,'2011-7-7 01:23:25' union all
select 10000001,100,'2011-7-18 01:23:25' union all
select 10000002,100,'2011-7-13 01:23:25' union all
select 10000005,89,'2011-7-20 01:23:25'--开始查询
;with cte as(
select rid=row_number() over (partition by a.用户姓名 order by newid()),a.*,b.消费金额,b.消费时间
from [用户信息表] a join [消费记录表] b on (a.用户卡号=b.用户卡号)
where b.消费金额=100
)
select 用户卡号,用户姓名,用户证件,消费金额,消费时间 from cte where rid=1--结束查询
drop table [用户信息表],[消费记录表]/*
用户卡号 用户姓名 用户证件 消费金额 消费时间
----------- ---- -------------------- ----------- -----------------------
10000001 张三 360502198511050011 100 2011-07-18 01:23:25.000
10000002 张四 360502198511050012 100 2011-07-16 01:23:25.000
10000003 张五 360502198511050013 100 2011-07-14 01:23:25.000(3 行受影响)
*
from
表1 a,表2 b
where
a.用户卡号=b.用户卡号
and
消费时间=(select max(消费时间) from 表2 where 用户卡号=b.用户卡号)
and
b.消费时间 between @bdate and @edate
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-03 08:40:52
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([用户姓名] varchar(4),[用户卡号] int,[消费金额] int,[消费时间] datetime)
insert [tb]
select '张五',10000003,100,'2011-7-14 01:23:25' union all
select '张四',10000002,9,'2011-7-1 01:23:25' union all
select '张三',10000001,100,'2011-7-8 01:23:25' union all
select '张三',10000001,89,'2011-7-7 01:23:25' union all
select '张四',10000002,100,'2011-7-2 01:23:25' union all
select '张六',10000004,9,'2011-7-6 01:23:25' union all
select '张四',10000002,100,'2011-7-21 01:23:25' union all
select '张七',10000005,89,'2011-7-6 01:23:25' union all
select '张四',10000002,100,'2011-7-7 01:23:25' union all
select '张七',10000005,9,'2011-7-10 01:23:25' union all
select '张四',10000002,100,'2011-7-16 01:23:25' union all
select '张五',10000003,89,'2011-7-7 01:23:25' union all
select '张七',10000002,100,'2011-7-7 01:23:25' union all
select '张三',10000001,100,'2011-7-18 01:23:25' union all
select '张四',10000002,100,'2011-7-13 01:23:25' union all
select '张七',10000005,89,'2011-7-20 01:23:25'
--------------开始查询--------------------------
select 用户姓名 ,用户卡号 , 消费金额,消费时间 from(select *,px=(select COUNT(用户姓名) from tb where 消费金额=100 and 用户卡号=t.用户卡号 and 消费时间<=t.消费时间) from (select * from tb where 消费金额=100)t)t where px=1
----------------结果----------------------------
/* 用户姓名 用户卡号 消费金额 消费时间
---- ----------- ----------- -----------------------
张五 10000003 100 2011-07-14 01:23:25.000
张三 10000001 100 2011-07-08 01:23:25.000
张四 10000002 100 2011-07-02 01:23:25.000(3 行受影响)*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([用户姓名] nvarchar(2),[用户卡号] int,[消费金额] int,[消费时间] Datetime)
Insert #T
select N'张五',10000003,100,'2011-7-14 01:23:25' union all
select N'张四',10000002,9,'2011-7-1 01:23:25' union all
select N'张三',10000001,100,'2011-7-8 01:23:25' union all
select N'张三',10000001,89,'2011-7-7 01:23:25' union all
select N'张四',10000002,100,'2011-7-2 01:23:25' union all
select N'张六',10000004,9,'2011-7-6 01:23:25' union all
select N'张四',10000002,100,'2011-7-21 01:23:25' union all
select N'张七',10000005,89,'2011-7-6 01:23:25' union all
select N'张四',10000002,100,'2011-7-7 01:23:25' union all
select N'张七',10000005,9,'2011-7-10 01:23:25' union all
select N'张四',10000002,100,'2011-7-16 01:23:25' union all
select N'张五',10000003,89,'2011-7-7 01:23:25' union all
select N'张七',10000002,100,'2011-7-7 01:23:25' union all
select N'张三',10000001,100,'2011-7-18 01:23:25' union all
select N'张四',10000002,100,'2011-7-13 01:23:25' union all
select N'张七',10000005,89,'2011-7-20 01:23:25'
Go
select
[用户姓名],[用户卡号],[消费金额],[消费时间]
from #T as a
where
not exists(select #t.* from #t where [用户卡号]=a.[用户卡号] and [消费金额]=100 and [消费时间]>a.[消费时间])
and a.[消费金额]=100
AND EXISTS(SELECT 1 FROM #t WHERE [用户卡号]=a.[用户卡号] HAVING COUNT(1)>1)[/code]
SELECT * FROM t1 a INNER JOIN tb b ON a.用户卡号=b.用户卡号
WHERE NOT EXISTS (SELECT TOP 1 * FROM tb WHERE 用户卡号=b.用户卡号 and 消费时间>b.消费时间)
AND b.消费金额 = '100'
--and b.消费时间 between @bdate and @edate