急求！！！！

[寂寞很巴黎]

tb1表
ID USER
1 A
2 B
3 C
4 D
tb2 表
ID USER
1 A
2 A
3 B
4 C
求tb2表中A的登录次数
打印如下
--------
ID USER 新列
1 A 2

[andysun88]

select  A,count(A)as 新列 from tb2 group by A

[王向飞]

select a.USER , count(b.USER)
from a ,b
where a.USER=b.USER
group by a.USER

[bluesmiler]

select a.USER , count(b.USER)
from a ,b
where a.USER=b.USER
and a.USER='A'
group by a.USER

[chuanzhang5687]

select  A,count(*)as 新列 from tb2 group by A

[oO寒枫Oo]

select a.ID, a.USER, count(a.USER) as xinlie
from tb2 b inner join tb1 a on a.USER=b.USER
group by a.ID, a.USER

[oO寒枫Oo]

select a.ID, a.USER, count(a.USER) as xinlie
from tb2 b inner join tb1 a on a.USER=b.USER
where b.USER='A'
group by a.ID, a.USER

[寂寞很巴黎]

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

SQL code
select A,count(*)as 新列 from tb2 group by A
[/Quote]
这个错的

[寂寞很巴黎]

我要求的tb1里所有登录USER的总次数 后面新加以个列名

[oO寒枫Oo]

[Quote=引用 8 楼 tangweihong1990 的回复:]
我要求的tb1里所有登录USER的总次数 后面新加以个列名
[/Quote]
试试这个

select a.ID, a.USER, count(a.USER) as xinlie
from tb2 b inner join tb1 a on a.USER=b.USER
where b.USER='A'
group by a.ID, a.USER

[寂寞很巴黎]

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

引用 8 楼 tangweihong1990 的回复:
我要求的tb1里所有登录USER的总次数 后面新加以个列名

试试这个
SQL code

select a.ID, a.USER, count(a.USER) as xinlie
from tb2 b inner join tb1 a on a.USER=b.USER
where b.USER='A'
group by a.ID……
[/Quote]
你这个 也错的啊

[Shawn]

SELECT * FROM 
(SELECT * FROM [tb1] WHERE [USER] = 'A') a
OUTER APPLY
(SELECT 新列 = COUNT(*) FROM [tb2] WHERE [USER] = a.[USER]) b

[Rich_er]

CREATE TABLE A(ID INT,USERNAME CHAR(10))
CREATE TABLE B(ID1 INT,USERNAME1 CHAR(10))
INSERT INTO A VALUES(1,'a'),(2,'a'),(3,'b'),(4,'b')
INSERT INTO B VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d')

SELECT ID1,USERNAME1,COUNT(USERNAME)AS 新列
FROM B
INNER JOIN A
ON
A.USERNAME=B.USERNAME1

GROUP BY ID1,USERNAME1
DROP TABLE A,B