select id,
num,
visitdate ,
(select count(*) from 表B b where convert(varchar(8),b.registedate,112 ) = convert(varchar(8),a.visitdate,112 ) and left(CONVERT(varchar(100), b.registedate, 12),2)=left(CONVERT(varchar(100), a.visitdate, 12),2))
from 表A a
num,
visitdate ,
(select count(*) from 表B b where convert(varchar(8),b.registedate,112 ) = convert(varchar(8),a.visitdate,112 ) and left(CONVERT(varchar(100), b.registedate, 12),2)=left(CONVERT(varchar(100), a.visitdate, 12),2))
from 表A a
解决方案 »
- 求助:数据类型转换
- 急等版主和高手!!!!一个让人头痛的SQL Server与datagrid问题
- 用T-SQL创建数据库出现,未能获得数据库'model'上的排它锁及未能创建所列出的某些文件名,这是为什么呢?
- 当使用 not in 时,子查询中有 null 时,为什么就不管用了
- 求一SQL语句
- sql批量替换问题
- 关于数据项的问题,高手请进阿!在线等待!!!
- 在news表中有一个DataTime类型的字段,如果“日”不足2位,怎样在“日”的前面加0呢?我的SQL语句:
- 统计问题,高手帮忙解决!!!
- 怎么样把Html的源文件插入到SQL里的一个字段上去啊?
- 高分求助:如何还原MS SQL 的差异还原?
- Winform如何远程连接SQL SERVER数据库
from A
select A.*, unum=(select count(*) from B where datediff(hh, B.registedate, A.visitdate)=0)
from A--try 2
select A.*, unum=count(*)
from A, B
where datediff(hh, B.registedate, A.visitdate)=0
group by A.id, A.num, A.visitdate
IF OBJECT_ID('TA')IS NOT NULL DROP TABLE TA
GO
CREATE TABLE TA(id INT, num INT, visitdate DATETIME)
INSERT TA SELECT 1 , 675, '2008-11-26 11:10:10'
INSERT TA SELECT 2 , 98 , '2008-11-26 12:00:10'
INSERT TA SELECT 3, 123 , '2008-11-26 13:02:10'
IF OBJECT_ID('TB')IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(id INT, registedate DATETIME)
INSERT TB SELECT 1 ,'2008-11-26 11:00:10'
INSERT TB SELECT 2 ,'2008-11-26 11:02:10'
INSERT TB SELECT 3 ,'2008-11-26 11:10:10'
INSERT TB SELECT 4 ,'2008-11-26 11:12:10'
INSERT TB SELECT 5 ,'2008-11-26 11:52:10'
INSERT TB SELECT 6 ,'2008-11-26 12:10:10'
INSERT TB SELECT 7 ,'2008-11-26 12:26:10'
INSERT TB SELECT 8 ,'2008-11-26 12:43:10'
INSERT TB SELECT 9 ,'2008-11-26 13:00:10'
INSERT TB SELECT 10 , '2008-11-26 13:10:10'
INSERT TB SELECT 11, '2008-11-26 13:40:10'
SELECT A.*,unum=(SELECT COUNT(*) FROM TB B WHERE convert(varchar(13),registedate,120 ) = convert(varchar(13),visitdate,120 )) FROM TA A
/*id num visitdate unum
----------- ----------- ------------------------------------------------------ -----------
1 675 2008-11-26 11:10:10.000 5
2 98 2008-11-26 12:00:10.000 3
3 123 2008-11-26 13:02:10.000 3*/
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
create table [A]([id] int,[num] int,[visitdate] datetime)
insert [A]
select 1,675,'2008-11-26 11:10:10' union all
select 2,98,'2008-11-26 12:00:10' union all
select 3,123,'2008-11-26 13:02:10'
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
create table [B]([id] int,[registedate] datetime)
insert [B]
select 1,'2008-11-26 11:00:10' union all
select 2,'2008-11-26 11:02:10' union all
select 3,'2008-11-26 11:10:10' union all
select 4,'2008-11-26 11:12:10' union all
select 5,'2008-11-26 11:52:10' union all
select 6,'2008-11-26 12:10:10' union all
select 7,'2008-11-26 12:26:10' union all
select 8,'2008-11-26 12:43:10' union all
select 9,'2008-11-26 13:00:10' union all
select 10,'2008-11-26 13:10:10' union all
select 11,'2008-11-26 13:40:10'--try 1
select A.*, unum=(select count(*) from B where datediff(hh, B.registedate, A.visitdate)=0)
from A--try 2
select A.*, unum=count(*)
from A, B
where datediff(hh, B.registedate, A.visitdate)=0
group by A.id, A.num, A.visitdate/*
id num visitdate unum
----------- ----------- ----------------------- -----------
1 675 2008-11-26 11:10:10.000 5
2 98 2008-11-26 12:00:10.000 3
3 123 2008-11-26 13:02:10.000 3(3 行受影响)id num visitdate unum
----------- ----------- ----------------------- -----------
1 675 2008-11-26 11:10:10.000 5
2 98 2008-11-26 12:00:10.000 3
3 123 2008-11-26 13:02:10.000 3(3 行受影响)
*/
from A
INSERT @TA
SELECT 1, 675, '2008-11-26 11:10:10' UNION ALL
SELECT 2, 98, '2008-11-26 12:00:10' UNION ALL
SELECT 3, 123, '2008-11-26 13:02:10'DECLARE @TB TABLE(id INT, registedate DATETIME)
INSERT @TB
SELECT 1, '2008-11-26 11:00:10' UNION ALL
SELECT 2, '2008-11-26 11:02:10' UNION ALL
SELECT 3, '2008-11-26 11:10:10' UNION ALL
SELECT 4, '2008-11-26 11:12:10' UNION ALL
SELECT 5, '2008-11-26 11:52:10' UNION ALL
SELECT 6, '2008-11-26 12:10:10' UNION ALL
SELECT 7, '2008-11-26 12:26:10' UNION ALL
SELECT 8, '2008-11-26 12:43:10' UNION ALL
SELECT 9, '2008-11-26 13:00:10' UNION ALL
SELECT 10, '2008-11-26 13:10:10' UNION ALL
SELECT 11, '2008-11-26 13:40:10'SELECT A.ID,A.NUM,COUNT(*) AS UNUM
FROM @TA AS A,@TB AS B
WHERE ABS(DATEDIFF(MI,visitdate,registedate))<=60 AND DATEPART(HH,registedate)=DATEPART(HH,visitdate)
GROUP BY A.ID,A.NUM,DATEPART(HH,registedate)
ORDER BY A.ID
/*
ID NUM UNUM
----------- ----------- -----------
1 675 5
2 98 3
3 123 3
*/
insert into a values(1, 675, '2008-11-26 11:10:10')
insert into a values(2, 98 , '2008-11-26 12:00:10')
insert into a values(3, 123, '2008-11-26 13:02:10')
create table b(id int, registedate datetime)
insert into b values(1 , '2008-11-26 11:00:10')
insert into b values(2 , '2008-11-26 11:02:10')
insert into b values(3 , '2008-11-26 11:10:10')
insert into b values(4 , '2008-11-26 11:12:10')
insert into b values(5 , '2008-11-26 11:52:10')
insert into b values(6 , '2008-11-26 12:10:10')
insert into b values(7 , '2008-11-26 12:26:10')
insert into b values(8 , '2008-11-26 12:43:10')
insert into b values(9 , '2008-11-26 13:00:10')
insert into b values(10, '2008-11-26 13:10:10')
insert into b values(11, '2008-11-26 13:40:10')
goselect a.* , unum = (select count(*) from b where convert(varchar(13) , registedate , 120) = convert(varchar(13) , visitdate , 120)) from a
/*
id num visitdate unum
----------- ----------- ------------------------------------------------------ -----------
1 675 2008-11-26 11:10:10.000 5
2 98 2008-11-26 12:00:10.000 3
3 123 2008-11-26 13:02:10.000 3
*/select a.* , unum = (select count(*) from b where datediff(hh, registedate , visitdate) = 0) from a
/*
id num visitdate unum
----------- ----------- ------------------------------------------------------ -----------
1 675 2008-11-26 11:10:10.000 5
2 98 2008-11-26 12:00:10.000 3
3 123 2008-11-26 13:02:10.000 3
*/drop table a,b
insert into a values(1, 675, '2008-11-26 11:10:10')
insert into a values(2, 98 , '2008-11-26 12:00:10')
insert into a values(3, 123, '2008-11-26 13:02:10')
create table b(id int, registedate datetime)
insert into b values(1 , '2008-11-26 11:00:10')
insert into b values(2 , '2008-11-26 11:02:10')
insert into b values(3 , '2008-11-26 11:10:10')
insert into b values(4 , '2008-11-26 11:12:10')
insert into b values(5 , '2008-11-26 11:52:10')
insert into b values(6 , '2008-11-26 12:10:10')
insert into b values(7 , '2008-11-26 12:26:10')
insert into b values(8 , '2008-11-26 12:43:10')
insert into b values(9 , '2008-11-26 13:00:10')
insert into b values(10, '2008-11-26 13:10:10')
insert into b values(11, '2008-11-26 13:40:10')
goselect a.* , unum = isnull((select count(*) from b where convert(varchar(13) , registedate , 120) = convert(varchar(13) , visitdate , 120)),0) from a
/*
id num visitdate unum
----------- ----------- ------------------------------------------------------ -----------
1 675 2008-11-26 11:10:10.000 5
2 98 2008-11-26 12:00:10.000 3
3 123 2008-11-26 13:02:10.000 3
*/select a.* , unum = isnull((select count(*) from b where datediff(hh, registedate , visitdate) = 0),0) from a
/*
id num visitdate unum
----------- ----------- ------------------------------------------------------ -----------
1 675 2008-11-26 11:10:10.000 5
2 98 2008-11-26 12:00:10.000 3
3 123 2008-11-26 13:02:10.000 3
*/drop table a,b