sqlserver联合两种多表查询:统计+关联查询

统计:
两张表a和b
a的字段如下：
a_id,a_name,a_address
b的字段如下：
b_id,b_phone,a_id
我要查询出两条字段的信息  分别是表a中的a_name和表b中的a_id的总数
比如a表中如果数据是这样子的：
a_id    a_name      a_address
1       me          beijing
2       wo          shanghai
3       he          guangzhou
4       she         shenzhen
5       our         hangzhou
表b中的数据如下：
b_id       b_phone  a_id
1          12345    1
2          23456    1
3          88888    1
4          99999    2
5          77777    3
我希望查询出这样的结果：
名字      数量
me        3
wo        1
he        1
-----------------------------------------------答

select a.a_name as 名字,count(b.a_id) as 数量 from a inner join b on a.a_id = b.a_id group by a.a_name

名字 数量
me   3
wo   1
he   1

select a.a_name as 名字,count(b.a_id) as 数量 from a left join b on a.a_id = b.a_id group by a.a_name

名字 数量
me   3
wo   1
he   1
she  0
our  0
来源:百度知道http://zhidao.baidu.com/question/218160255.html

.............................................................................................................
关联
点击数据库的sql窗格

 public DataTable selectNotAllowAppClick(int NO)
        {
            string sql = string.Format("SELECT NO, UserId, TrueName, LeaveTime, LeaveType, AppDays, LeaveDescription,AddTime FROM leaveMessage WHERE NO = @NO");
            DataConn conn = new DataConn(conStr);
            ArrayList paramlist = new ArrayList();
            paramlist.Add(conn.CreateParameter("@NO", NO, DbType.UInt16, 4));
            IDataParameter[] param = (IDataParameter[])paramlist.ToArray(typeof(IDataParameter));
            return conn.testDataTable(sql, param);
        }

选中全部代码,右击"在编辑器中设计查询",将UserId关联

再加上where条件语句:     WHERE     (demoUsers.ChineseWord = 'f')

 

执行结果如下

 

SELECT     AttendanceRecord.AttendanceId, AttendanceRecord.UserId, AttendanceRecord.OnDutyTime, AttendanceRecord.OffDutyTime, 
                      AttendanceRecord.OverTimeStart, AttendanceRecord.OverTimeEnd, AttendanceRecord.RecordTime, AttendanceRecord.ComeLate, 
                      AttendanceRecord.GoEarly, (CASE AttendanceRecord.ComeLate WHEN 1 THEN '是' ELSE '否' END) AS test
FROM         AttendanceRecord INNER JOIN
                      demoUsers ON AttendanceRecord.UserId = demoUsers.UserId
WHERE     (demoUsers.ChineseWord = 'cs')