select * from in_detail a where not exists (select 1 from in_detail where no=a.no and intime>a.intime)
SELECT no, name, MAX(intime) FROM dbo.in_detail GROUP BY no, name
SELECT A.u_name,A.price,A.intime FROM #in_detail A, (SELECT U_NAME,MAX(INTIME)INTIME FROM #in_detail GROUP BY U_NAME) B WHERE A.u_name = B.U_NAME AND A.intime = B.INTIME
--创建in_detail表 CREATE TABLE #in_detail(no INT,u_name NVARCHAR(10),unit NVARCHAR(10),price decimal(18,2),amount INT,intime DATETIME) --插入user表数据 INSERT INTO #in_detail SELECT 1,'电脑','A组',100,10,'2013-04-01' UNION ALL SELECT 2,'电脑','B组',200,12,'2013-04-02' UNION ALL SELECT 3,'电脑','C组',300,12,'2013-04-03' UNION ALL SELECT 4,'空调','A组',300,12,'2013-04-01' UNION ALL SELECT 5,'空调','B组',300,12,'2013-04-02' UNION ALL SELECT 6,'空调','C组',300,12,'2013-04-03' SELECT A.u_name,A.price,A.intime FROM #in_detail A, (SELECT U_NAME,MAX(INTIME)INTIME FROM #in_detail GROUP BY U_NAME) B WHERE A.u_name = B.U_NAME AND A.intime = B.INTIME
select * from 仓库明细表 where inttime in ( select max(inttime) from 仓库明细表)
--创建in_detail表 CREATE TABLE #in_detail(no INT,u_name NVARCHAR(10),unit NVARCHAR(10),price decimal(18,2),amount INT,intime DATETIME) --插入user表数据 INSERT INTO #in_detail SELECT 1,'电脑','A组',100,10,'2013-04-01' UNION ALL SELECT 2,'电脑','B组',200,12,'2013-04-02' UNION ALL SELECT 3,'电脑','C组',300,12,'2013-04-03' UNION ALL SELECT 4,'空调','A组',300,12,'2013-04-01' UNION ALL SELECT 5,'空调','B组',300,12,'2013-04-02' UNION ALL SELECT 6,'空调','C组',300,12,'2013-04-03'SELECT * FROM #in_detail a WHERE EXISTS ( SELECT 1 FROM ( SELECT u_name , MAX(intime) intime FROM #in_detail GROUP BY u_name ) b WHERE a.u_name = b.u_name AND a.intime = b.intime )
if object_id(N'#in_detail',N'u') is not null drop table #in_detail create table #in_detail (no int,u_name varchar(10),unit varchar(10),price money,amount int,intime datetime) insert into #in_detail select 1,'电脑','A组',100,10,'2013-04-01' union all select 2,'电脑','B组',200,12,'2013-04-02' union all select 3,'电脑','C组',300,12,'2013-04-03' union all select 4,'空调','A组',300,12,'2013-04-01' union all select 5,'空调','B组',300,12,'2013-04-02' union all select 6,'空调','C组',300,12,'2013-04-03'select * from #in_detail select * from #in_detail as a where not exists(select * from #in_detail where intime>a.intime) select * from #in_detail as a where exists(select * from (select u_name,max(intime) intime from #in_detail group by u_name) b where a.u_name = b.u_name and a.intime = b.intime)
select name,price,intime FROM #in_detail A where exists( (select name,max(intime) from #in_detail group by name)B where A.name=B.name and A.intime=B.max(intime) )
SELECT A.name,A.price,A.intime FROM in_detail A, (SELECT NAME,MAX(INTIME)INTIME FROM in_detail GROUP BY NAME) B WHERE A.name = B.NAME AND A.intime = B.INTIME
where not exists
(select 1 from in_detail where no=a.no and intime>a.intime)
FROM dbo.in_detail
GROUP BY no, name
(SELECT U_NAME,MAX(INTIME)INTIME FROM #in_detail GROUP BY U_NAME) B
WHERE A.u_name = B.U_NAME AND A.intime = B.INTIME
CREATE TABLE #in_detail(no INT,u_name NVARCHAR(10),unit NVARCHAR(10),price decimal(18,2),amount INT,intime DATETIME)
--插入user表数据
INSERT INTO #in_detail
SELECT 1,'电脑','A组',100,10,'2013-04-01'
UNION ALL
SELECT 2,'电脑','B组',200,12,'2013-04-02'
UNION ALL
SELECT 3,'电脑','C组',300,12,'2013-04-03'
UNION ALL
SELECT 4,'空调','A组',300,12,'2013-04-01'
UNION ALL
SELECT 5,'空调','B组',300,12,'2013-04-02'
UNION ALL
SELECT 6,'空调','C组',300,12,'2013-04-03'
SELECT A.u_name,A.price,A.intime FROM #in_detail A,
(SELECT U_NAME,MAX(INTIME)INTIME FROM #in_detail GROUP BY U_NAME) B
WHERE A.u_name = B.U_NAME AND A.intime = B.INTIME
select max(inttime) from 仓库明细表)
CREATE TABLE #in_detail(no INT,u_name NVARCHAR(10),unit NVARCHAR(10),price decimal(18,2),amount INT,intime DATETIME)
--插入user表数据
INSERT INTO #in_detail
SELECT 1,'电脑','A组',100,10,'2013-04-01'
UNION ALL
SELECT 2,'电脑','B组',200,12,'2013-04-02'
UNION ALL
SELECT 3,'电脑','C组',300,12,'2013-04-03'
UNION ALL
SELECT 4,'空调','A组',300,12,'2013-04-01'
UNION ALL
SELECT 5,'空调','B组',300,12,'2013-04-02'
UNION ALL
SELECT 6,'空调','C组',300,12,'2013-04-03'SELECT *
FROM #in_detail a
WHERE EXISTS ( SELECT 1
FROM ( SELECT u_name ,
MAX(intime) intime
FROM #in_detail
GROUP BY u_name
) b
WHERE a.u_name = b.u_name
AND a.intime = b.intime )
/*
no u_name unit price amount intime
----------- ---------- ---------- --------------------------------------- ----------- -----------------------
3 电脑 C组 300.00 12 2013-04-03 00:00:00.000
6 空调 C组 300.00 12 2013-04-03 00:00:00.000(2 行受影响)
*/
drop table #in_detail
create table #in_detail
(no int,u_name varchar(10),unit varchar(10),price money,amount int,intime datetime)
insert into #in_detail
select 1,'电脑','A组',100,10,'2013-04-01' union all
select 2,'电脑','B组',200,12,'2013-04-02' union all
select 3,'电脑','C组',300,12,'2013-04-03' union all
select 4,'空调','A组',300,12,'2013-04-01' union all
select 5,'空调','B组',300,12,'2013-04-02' union all
select 6,'空调','C组',300,12,'2013-04-03'select * from #in_detail
select * from #in_detail as a where not exists(select * from #in_detail where intime>a.intime)
select * from #in_detail as a
where exists(select * from (select u_name,max(intime) intime from #in_detail group by u_name) b
where a.u_name = b.u_name and a.intime = b.intime)
where exists(
(select name,max(intime) from #in_detail group by name)B
where A.name=B.name and A.intime=B.max(intime)
)
(SELECT NAME,MAX(INTIME)INTIME FROM in_detail GROUP BY NAME) B
WHERE A.name = B.NAME AND A.intime = B.INTIME