表如下 name ,id,datetime
a b 2001-1-1 14:20:20
a b 2001-1-1 14:22:50 c c 2001-2-2 12:20:34
c c 2001-2-2 12:50:20我要的结果如下
a b 2001-1-1 14:20:20
c c 2001-2-2 12:20:34怎么写啊。大下门帮帮啊
a b 2001-1-1 14:20:20
a b 2001-1-1 14:22:50 c c 2001-2-2 12:20:34
c c 2001-2-2 12:50:20我要的结果如下
a b 2001-1-1 14:20:20
c c 2001-2-2 12:20:34怎么写啊。大下门帮帮啊
解决方案 »
- 为什么sqlconnection SqlDataAdapter DataSet关闭后句柄和内存还不释放
- 发布一个.net代码保护软件,来顶的都有分
- 制作代码编辑器中,征集代码字体样式!100分!!!
- C#读写文件 送分啊!
- VISTA系统 为什么看不到windows身份验证选项
- win小程序,判断手机上10条数据有无重复数据的高效率写法,跪求解决方案...谢谢
- 菜鸟的继承问题?
- 怎么将一个字符串存到一个EXCEL表里的第一行一列中,而且以后的字符串是追加的方式写入
- 在线求救?
- 关于pe格式的文件
- 我是NHibernate初学者。能帮我看看代码有什么错误吗?很短的代码,但就是出不来。我都快哭了!!:(
- 将实现了ILis的对象绑定到数据源上的问题
FROM a
GROUP BY name,id
Having COUNT(id)>1
select * from tb a where not exists( select * from tb where name = a.name and id = a.id and datetime>a.datetime)
我曾经写了个200行的SQL语句;没有用一个临时表。
那我这能说楼主没有很好的大学本科教育 或者上学时光顾着睡觉了 极度鄙视示例
下例返回 {(a,b), (c,d )}:Distinct({(a,b), (c,d), (a,b)})这是引自 sqlserver transact-sql帮助中
set @month='2005-4-1'
select @month as 月份,dpname1 as 部门,isnull(开户人次,0) as 开户人次,isnull(开户后第一次存款额,0) as 开户后第一次存款额,isnull(消费额,0) as 消费额,
isnull(消费次数,0) as 消费次数,isnull(存取款额,0) as 存取款额,isnull(存取款次数,0) as 存取款次数,isnull(卡余额总额,0) as 卡余额总额
from (select distinct dpcode1,dpname1 from T_Department) Department left outer join (SELECT DpCode1, kh_month, COUNT(*) AS 开户人次, SUM(in_out_fare)
AS 开户后第一次存款额
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(T_Customers.OpenDt) AS char))
+ '-' + RTRIM(CAST(MONTH(T_Customers.OpenDt) AS char))
+ '-' + RTRIM(DAY(0)) AS kh_month, min_in_out_fare.in_out_fare
FROM T_Customers INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON
T_Customers.Account = dep.dpcode left outer JOIN
(SELECT min_opcount.CustomerID,
T_CashRec.InFare - T_CashRec.OutFare in_out_fare
FROM (SELECT CustomerID, MIN(OpCount) AS min_opcount
FROM T_CashRec
GROUP BY CustomerID) min_opcount INNER JOIN
T_CashRec ON
min_opcount.CustomerID = T_CashRec.CustomerID AND
min_opcount.min_opcount = T_CashRec.OpCount) min_in_out_fare ON
min_in_out_fare.CustomerID = T_Customers.CustomerID)
一级单位月开户明细
GROUP BY DpCode1, kh_month having kh_month=@month/*一级单位月开户汇总*/
) kh on kh.dpcode1=Department.dpcode1 left outer join (SELECT DpCode1, xf_month, SUM(OpFare) AS 消费额,count(*) as 消费次数
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(consumerec.OpDt) AS char))
+ '-' + RTRIM(CAST(MONTH(consumerec.OpDt) AS char)) + '-' + RTRIM(DAY(0))
AS xf_month, consumerec.OpFare
FROM T_ConsumeRec consumerec INNER JOIN
T_Customers ON
consumerec.CustomerID = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode)
一级单位月消费明细
GROUP BY DpCode1, xf_month having xf_month=@month /*一级单位月消费汇总*/
) xf on xf.dpcode1=Department.dpcode1 left outer join (SELECT DpCode1, cqk_month, SUM(inFare - outFare) AS 存取款额,count(*) as 存取款次数
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(consumerec.cashdt) AS char))
+ '-' + RTRIM(CAST(MONTH(consumerec.cashdt) AS char))
+ '-' + RTRIM(DAY(0)) AS cqk_month, consumerec.inFare,
consumerec.outFare
FROM T_CashRec consumerec INNER JOIN
T_Customers ON
consumerec.CustomerID = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode)
一级单位月存取款明细
GROUP BY DpCode1, cqk_month having cqk_month=@month/*一级单位月存取款汇总*/
) cq on cq.dpcode1=Department.dpcode1 left outer join (SELECT dep.DpCode1, sum(id_MaxO.OddFare) as 卡余额总额
FROM (SELECT id_m_maxC.customerid, id_c_o.OddFare
FROM (SELECT customerid, MAX(OpCount) AS max_opcount
FROM (SELECT CustomerID, OpCount, RTRIM(CAST(YEAR(Dt) AS char))
+ '-' + RTRIM(CAST(MONTH(Dt) AS char)) + '-' + RTRIM(DAY(0))
AS month
FROM (SELECT CustomerID, OpCount, OpDt AS dt
FROM T_ConsumeRec
UNION ALL
SELECT CustomerID, OpCount, cashDt AS dt
FROM T_cashRec
UNION ALL
SELECT CustomerID, OpCount, putoutDt AS dt
FROM T_subsidyputout) id_c_d) id_c_m where month <= @month/*月份参数*/
GROUP BY customerid
) id_m_maxC INNER JOIN
(SELECT CustomerID, OpCount, OddFare
FROM (SELECT CustomerID, OpCount, OddFare
FROM T_ConsumeRec
UNION ALL
SELECT CustomerID, OpCount, OddFare
FROM T_cashRec
UNION ALL
SELECT CustomerID, OpCount, OddFare
FROM T_subsidyputout) Lid_c_o) id_c_o ON
id_c_o.CustomerID = id_m_maxC.customerid AND
id_c_o.OpCount = id_m_maxC.max_opcount) id_MaxO INNER JOIN
T_Customers ON id_MaxO.customerid = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode/*一级单位在某月份的卡余额明细*/
group by dep.DpCode1 /*一级单位在某月份的卡余额汇总*/) kye on kye.dpcode1=Department.dpcode1
执行后的示例数据:月份 部门 开户人次 开户后第一次存款额 消费额 消费次数 存取款额 存取款次数 卡余额总额
2005-4-1 职工卡 4 ¥2,400.00 ¥7,728.29 1054 ¥531,369.40 1112 ¥523,937.84
2005-4-1 职工卡2 0 ¥0.00 ¥0.00 0 ¥0.00 0 ¥0.00
2005-4-1 外单位人员 100 ¥620.00 ¥0.00 0 ¥620.00 4 ¥620.00
2005-4-1 挂帐卡 0 ¥0.00 ¥0.00 0 ¥0.00 0 ¥0.00
2005-4-1 现金卡 2 ¥0.00 ¥0.00 0 ¥0.00 0 ¥0.00
2005-4-1 折扣卡 56 ¥16,500.00 ¥984.40 152 ¥16,500.00 55 ¥15,515.60
2005-4-1 集团代办卡 0 ¥0.00 ¥0.00 0 ¥0.00 0 ¥0.00
呵呵,比较有意思~~ 存取过程都拿出来了
呵呵,比较有意思~~ 存取过程都拿出来了...............................sigh....
大家就趁着关税的功夫都看点书吧sigh
select * from tb a where not exists( select * from tb where name = a.name and id = a.id and datetime>a.datetime)
select * from tb a where not exists( select * from tb where name = a.name and id = a.id and datetime<a.datetime)
from TABLE
group by name,idselect *
from tb a
where not exists(
select *
from tb
where name = a.name and id = a.id and datetime>a.datetime)在建立合适的key情况下:name+id,第一种执行的效率应该比第二种高