select a.emp_id,a.emp_name,b.work_date,b.start_time,b.end_time from A a left join B b on a.emp_id = b.emp_id
select a.emp_id,a.emp_name,b.work_date,b.start_time,b.end_time from a,b where a.emp_id=b.emp_id
加了where 条件,3和4的记录不能查出来的
declare @ta table(emp_id int, emp_name varchar(10)) insert @ta SELECT 1, 'marry' UNION ALL SELECT 2, 'tom' UNION ALL SELECT 3, 'jack' UNION ALL SELECT 4, 'emie' declare @tb table(emp_id int, work_date smalldatetime, start_time varchar(20), end_time varchar(20)) insert @tb SELECT 1, '2005-10-01', '08:30:00', '17:00:25' UNION ALL SELECT 1, '2005-10-02', '08:24:00', '17:35:25' UNION ALL SELECT 2, '2005-10-02', '08:25:04', '17:56:30' select a.emp_id,emp_name,isnull(work_date,'2005-10-02') as work_date,isnull(start_time, '') as start_time,isnull(end_time,'') as end_time from @ta as a left join (select * from @tb where work_date='2005-10-02') as b on a.emp_id=b.emp_id /* emp_id emp_name work_date start_time end_time ----------- ---------- ------------------------------------------------------ -------------------- -------------------- 1 marry 2005-10-02 00:00:00 08:24:00 17:35:25 2 tom 2005-10-02 00:00:00 08:25:04 17:56:30 3 jack 2005-10-02 00:00:00 4 emie 2005-10-02 00:00:00 */
select a.emp_id,a.emp_name,b.work_date,b.start_time,b.end_time from a left join (select * from b where work_date='2005-10-02')b on a.emp_id=b.emp_id
select a.*,b.work_date,b.start_time,b.end_time from a left join b on a.emp_id=b.emp_id and datediff(dd,'2005-10-02',b.work_date)=0
--> liangCK小梁 于2008-10-21 --> 生成测试数据: #tba IF OBJECT_ID('tempdb.dbo.#tba') IS NOT NULL DROP TABLE #tba CREATE TABLE #tba (emp_id INT,emp_name VARCHAR(5)) INSERT INTO #tba SELECT 1,'marry' UNION ALL SELECT 2,'tom' UNION ALL SELECT 3,'jack' UNION ALL SELECT 4,'emie' --> liangCK小梁 于2008-10-21 --> 生成测试数据: #tbb IF OBJECT_ID('tempdb.dbo.#tbb') IS NOT NULL DROP TABLE #tbb CREATE TABLE #tbb (emp_id INT,work_date DATETIME,start_time DATETIME,end_time DATETIME) INSERT INTO #tbb SELECT 1,'2005-10-01', '08:30:00','17:00:25' UNION ALL SELECT 1,'2005-10-02', '08:24:00','17:35:25' UNION ALL SELECT 2,'2005-10-02', '08:25:04','17:56:30'--SQL查询如下:SELECT a.emp_id,a.emp_name, ISNULL(CONVERT(VARCHAR(10),b.work_date,120),'2005-10-02') work_date, CONVERT(VARCHAR(10),b.start_time,108) start_time, CONVERT(VARCHAR(10),b.end_time,108) end_time FROM #tba AS a LEFT OUTER JOIN #tbb AS b ON a.emp_id=b.emp_id AND DATEDIFF(DAY,b.work_date,'2005-10-02')=0 /* emp_id emp_name work_date start_time end_time ----------- -------- ---------- ---------- ---------- 1 marry 2005-10-02 08:24:00 17:35:25 2 tom 2005-10-02 08:25:04 17:56:30 3 jack 2005-10-02 NULL NULL 4 emie 2005-10-02 NULL NULL(4 行受影响)*/
Select e.emp_id,e.emp_name,@workdate,isnull(start_time,''),isnull(end_time ,'') From c left Join ( select a.emp_id,a.emp_name,work_date,start_time,end_time from a left join b on a.emp_id=b.emp_id where work_date=@workdate)e On c.emp_id=e.emp_id And c.work_date=e.work_date
可是我这个是直接在代码里写的,没有存储过程,能用@workdate这样的变量吗?
既然是查询的话在前台总有个输入框吧,如edit1.text 就相当于@workdate变量啊。
select ta.emp_id,ta.emp_name,tb.work_date,start_time=isnull(b.start_time,‘’),isnull(b.end_time,‘’) from a,b where a.emp_id=b.emp_id
from A a left join B b
on a.emp_id = b.emp_id
from a,b where a.emp_id=b.emp_id
declare @ta table(emp_id int, emp_name varchar(10))
insert @ta
SELECT 1, 'marry' UNION ALL
SELECT 2, 'tom' UNION ALL
SELECT 3, 'jack' UNION ALL
SELECT 4, 'emie'
declare @tb table(emp_id int, work_date smalldatetime, start_time varchar(20), end_time varchar(20))
insert @tb
SELECT 1, '2005-10-01', '08:30:00', '17:00:25' UNION ALL
SELECT 1, '2005-10-02', '08:24:00', '17:35:25' UNION ALL
SELECT 2, '2005-10-02', '08:25:04', '17:56:30'
select a.emp_id,emp_name,isnull(work_date,'2005-10-02') as work_date,isnull(start_time, '') as start_time,isnull(end_time,'') as end_time
from @ta as a left join (select * from @tb where work_date='2005-10-02') as b on a.emp_id=b.emp_id
/*
emp_id emp_name work_date start_time end_time
----------- ---------- ------------------------------------------------------ -------------------- --------------------
1 marry 2005-10-02 00:00:00 08:24:00 17:35:25
2 tom 2005-10-02 00:00:00 08:25:04 17:56:30
3 jack 2005-10-02 00:00:00
4 emie 2005-10-02 00:00:00
*/
from a left join (select * from b where work_date='2005-10-02')b
on a.emp_id=b.emp_id
on a.emp_id=b.emp_id and datediff(dd,'2005-10-02',b.work_date)=0
--> 生成测试数据: #tba
IF OBJECT_ID('tempdb.dbo.#tba') IS NOT NULL DROP TABLE #tba
CREATE TABLE #tba (emp_id INT,emp_name VARCHAR(5))
INSERT INTO #tba
SELECT 1,'marry' UNION ALL
SELECT 2,'tom' UNION ALL
SELECT 3,'jack' UNION ALL
SELECT 4,'emie'
--> liangCK小梁 于2008-10-21
--> 生成测试数据: #tbb
IF OBJECT_ID('tempdb.dbo.#tbb') IS NOT NULL DROP TABLE #tbb
CREATE TABLE #tbb (emp_id INT,work_date DATETIME,start_time DATETIME,end_time DATETIME)
INSERT INTO #tbb
SELECT 1,'2005-10-01', '08:30:00','17:00:25' UNION ALL
SELECT 1,'2005-10-02', '08:24:00','17:35:25' UNION ALL
SELECT 2,'2005-10-02', '08:25:04','17:56:30'--SQL查询如下:SELECT a.emp_id,a.emp_name,
ISNULL(CONVERT(VARCHAR(10),b.work_date,120),'2005-10-02') work_date,
CONVERT(VARCHAR(10),b.start_time,108) start_time,
CONVERT(VARCHAR(10),b.end_time,108) end_time
FROM #tba AS a
LEFT OUTER JOIN #tbb AS b
ON a.emp_id=b.emp_id
AND DATEDIFF(DAY,b.work_date,'2005-10-02')=0
/*
emp_id emp_name work_date start_time end_time
----------- -------- ---------- ---------- ----------
1 marry 2005-10-02 08:24:00 17:35:25
2 tom 2005-10-02 08:25:04 17:56:30
3 jack 2005-10-02 NULL NULL
4 emie 2005-10-02 NULL NULL(4 行受影响)*/
ISNULL(CONVERT(VARCHAR(10),b.work_date,120),'2005-10-02') work_date,
是有问题的
From c
left Join
(
select a.emp_id,a.emp_name,work_date,start_time,end_time
from a
left join b on a.emp_id=b.emp_id
where work_date=@workdate)e On c.emp_id=e.emp_id And c.work_date=e.work_date
既然是查询的话在前台总有个输入框吧,如edit1.text 就相当于@workdate变量啊。
from a,b
where a.emp_id=b.emp_id