34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-22 12:49:36
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[customername] varchar(2),[mobile] int,[Product] varchar(5),[购买时间] datetime)
insert [huang]
select 1,'x1',1111111,'商品1','2014.1.1' union all
select 2,'x2',2222222,'商品2','2014.1.2' union all
select 3,'x3',3333333,'商品3','2014.1.3' union all
select 4,'x1',1111111,'商品4','2014.1.4'
--------------开始查询--------------------------
select * from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT [customername],[mobile],MAX([购买时间])[购买时间] FROM huang GROUP BY [customername],[mobile]) b
WHERE a.[customername]=b.[customername] AND a.[mobile]=b.[mobile] AND a.[购买时间]=b.[购买时间])
----------------结果----------------------------
/*
id customername mobile Product 购买时间
----------- ------------ ----------- ------- -----------------------
2 x2 2222222 商品2 2014-01-02 00:00:00.000
3 x3 3333333 商品3 2014-01-03 00:00:00.000
4 x1 1111111 商品4 2014-01-04 00:00:00.000
*/
IF OBJECT_ID(N'Test',N'U') IS NOT NULL
DROP TABLE Test
GO
CREATE TABLE Test
(id INT IDENTITY(1,1),
name VARCHAR(10),
mobile VARCHAR(20),
Product VARCHAR(50)
)
INSERT INTO Test
SELECT 'x1' ,'1111111', '商品1' UNION ALL
SELECT 'x2' ,'2222222', '商品2' UNION ALL
SELECT 'x3' ,'3333333', '商品3' UNION ALL
SELECT 'x1' ,'1111111', '商品4'
SELECT MAX(id) AS id,name,mobile,MAX(Product) AS Product,MAX(sort) '详细购买记录',CASE WHEN MAX(sort)>1 THEN 1 ELSE 0 END flag FROM (
SELECT id,NAME,mobile,product,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) sort
FROM Test) a GROUP BY NAME,mobile ORDER BY id DESC
/*
id name mobile Product 详细购买记录 flag
----------- ---------- -------------------- -------------------------------------------------- -------------------- -----------
4 x1 1111111 商品4 2 1
3 x3 3333333 商品3 1 0
2 x2 2222222 商品2 1 0
(3 行受影响)
*/
select a.*,b.flag
from Customer a,(select id=max(id),flag=count(*)
from Customer
group by name,mobile) b
where a.id=b.id
这样看看
create table test(id int, name nvarchar(5),mobile nvarchar(15),product nvarchar(10))
insert into test values(1,'x1','1111111','商品1')
insert into test values(2,'x2','2222222','商品2')
insert into test values(3,'x3','3333333','商品3')
insert into test values(4,'x1','1111111','商品4')
--去重显示 同一客户只显示最近购买的那条记录,
select id=MAX(id),
name,
mobile,
Product=(select product from test a where a.id=MAX(b.id) ),
产品个数=COUNT(*)
from test b group by name,mobile
/*
4 x1 1111111 商品4
2 x2 2222222 商品2
3 x3 3333333 商品3
*/
IF OBJECT_ID(N'Test',N'U') IS NOT NULL
DROP TABLE Test
GO
CREATE TABLE Test
(id INT IDENTITY(1,1),
name VARCHAR(10),
mobile VARCHAR(20),
Product VARCHAR(50)
)
INSERT INTO Test
SELECT 'x1' ,'1111111', '商品1' UNION ALL
SELECT 'x2' ,'2222222', '商品2' UNION ALL
SELECT 'x3' ,'3333333', '商品3' UNION ALL
SELECT 'x1' ,'1111111', '商品4'
SELECT MAX(id) AS id,name,MAX(mobile) AS mobile,MAX(Product) AS Product,MAX(sort) '详细购买记录' FROM (
SELECT id,NAME,mobile,product,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) sort
FROM Test) a GROUP BY NAME ORDER BY id DESC
/*
id name mobile Product 详细购买记录
----------- ---------- -------------------- -------------------------------------------------- --------------------
4 x1 1111111 商品4 2
3 x3 3333333 商品3 1
2 x2 2222222 商品2 1
(3 行受影响)
*/