这个sql怎么写

[naonaoye]

表1
id name
1 aaa
2 bbb
3 ccc

表2
id time
1 110707
1 110708
2 110707

要gridview中显示为
id name times
1 aaa 2
2 bbb 1
3 ccc 0
sql语句怎么写？能不能都写在一个存储过程里？

[hch126163]

select t1.id,t1.name,(select isnull(count(1),0) from t2 where t1.id=t2.id) as times
from t1

[q107770540]

left join+group by

[子夜__]

[Quote=引用 1 楼 hch126163 的回复:]

select t1.id,t1.name,(select isnull(count(1),0) from t2 where t1.id=t2.id) as times
from t1
[/Quote]
没问题。。

[q107770540]

[Quote=引用 1 楼 hch126163 的回复:]

select t1.id,t1.name,(select isnull(count(1),0) from t2 where t1.id=t2.id) as times
from t1
[/Quote]
++

帖个完整实例：

CREATE	TABLE t1 (
	 id INT,
	 NAME NVARCHAR (20)
)

CREATE TABLE t2
(
	id INT,
	TIME int
	)
	
INSERT INTO t1 SELECT 1,'aaa' UNION ALL
SELECT 2,'bbb' UNION ALL
SELECT 3,'ccc'

INSERT INTO t2 SELECT 1,110707  UNION ALL
SELECT 1,110708 UNION ALL 
SELECT 2,110707
	
	
	select t1.id,t1.name,(select isnull(count(1),0) from t2 where t1.id=t2.id) as times
from t1

id          name                 times
----------- -------------------- -----------
1           aaa                  2
2           bbb                  1
3           ccc                  0

(3 row(s) affected)

[qgqch2008]

CREATE PROCEDURE [dbo].[grid]
AS
BEGIN
SELECT a.id,a.NAME,(SELECT COUNT(1) FROM dbo.t2 b WHERE b.id=a.id ) AS times FROM dbo.t1 a
END;
EXEC dbo.grid
/*
id          NAME                 times
----------- -------------------- -----------
1           aaa                  2
2           bbb                  1
3           ccc                  0

(3 個資料列受到影響)
*/

是不是用視圖更好呢？

[快溜]

CREATE    TABLE t1 (
     id INT,
     NAME NVARCHAR (20)
)

CREATE TABLE t2
(
    id INT,
    TIME int
    )
    
INSERT INTO t1 SELECT 1,'aaa' UNION ALL
SELECT 2,'bbb' UNION ALL
SELECT 3,'ccc'

INSERT INTO t2 SELECT 1,110707  UNION ALL
SELECT 1,110708 UNION ALL 
SELECT 2,110707

select a.*,times=sum(case when b.id is null then 0 else 1 end)
from t1 a left join t2 b on a.id=b.id
group by a.id,a.name

/*
id          NAME                 times
----------- -------------------- -----------
1           aaa                  2
2           bbb                  1
3           ccc                  0

[laowang134]

[Quote=引用 6 楼 ssp2009 的回复:]
SQL code

CREATE TABLE t1 (
id INT,
NAME NVARCHAR (20)
)

CREATE TABLE t2
(
id INT,
TIME int
)

INSERT INTO t1 SELECT 1,'aaa' UNION ALL
SELECT 2,'bbb' UNION ……
[/Quote]
+1
用连接查询比用关联子查询好！~

[aXen]

SELECT t.id, 表1.name, t.time2
FROM (
    SELECT id, COUNT(id) AS times FROM 表2 GROUP BY time
) AS t LEFT JOIN 表1 ON t.id = 表1.id

另一种写法，不知道符不符合要求。

[子夜__]

[Quote=引用 4 楼 q107770540 的回复:]

引用 1 楼 hch126163 的回复:

select t1.id,t1.name,(select isnull(count(1),0) from t2 where t1.id=t2.id) as times
from t1

++

帖个完整实例：

SQL code

CREATE TABLE t1 (
id INT,
NAME NVARCH……
[/Quote]
没打算用linq来写么？

[挨踢直男]

select 表1.id,表1.name,count(time) as times from  表1 left join 表2 on 表1.id = 表2.id group by 表1.id ,表1.name

[ASPNETCHENGXU]

分组字段加，关联表查询

[q107770540]

[Quote=引用 9 楼 wxr0323 的回复:]

引用 4 楼 q107770540 的回复:

引用 1 楼 hch126163 的回复:

select t1.id,t1.name,(select isnull(count(1),0) from t2 where t1.id=t2.id) as times
from t1

++

帖个完整实例：

SQL code

CREATE TABLE t1 (
i……
[/Quote]
R ...LZ要的是SQL

[kiss筱魔]

CREATE TABLE t1 (
id INT,
NAME NVARCHAR (20)
)

CREATE TABLE t2
(
id INT,
TIME int
)

INSERT INTO t1 SELECT 1,'aaa' UNION ALL
SELECT 2,'bbb' UNION ALL
SELECT 3,'ccc'

INSERT INTO t2 SELECT 1,110707 UNION ALL
SELECT 1,110708 UNION ALL
SELECT 2,110707


select t1.id,t1.name,(select isnull(count(1),0) from t2 where t1.id=t2.id) as times
from t1

id name times
----------- -------------------- -----------
1 aaa 2
2 bbb 1
3 ccc 0

(3 row(s) affected)

[naonaoye]

多谢各位的指点，结贴

[FlyHigherForever]

SELECT A.ID,A.name,COUNT(B.ID) FROM A LEFT JOIN B ON A.id=B.ID GROUP BY A.ID,A.name

//A 为第一张表 B为第二张表