Join 语句

select * from books as A join (select * from Orders) as B on A.BookId = B.BookId  

select  A.BookId,Authorname,title,Address,Num  from books A right join (select  *  from Orders) as B on A.BookId = B.BookId

存储过程 jon
exec sp_executesql N'SELECT 
    [Extent1].[OrderId] AS [OrderId], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[Num] AS [Num], 
    [Extent2].[BookId] AS [BookId], 
    [Extent2].[AuthorName] AS [AuthorName], 
    [Extent2].[Title] AS [Title]
    FROM  [dbo].[Orders] AS [Extent1]
    INNER JOIN [dbo].[books] AS [Extent2] ON [Extent1].[BookId] = [Extent2].[BookId]
    WHERE [Extent1].[BookId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=1

 //使用join连接   //查询订单中的指定Bookid的数据  join  图书表中的指定bookid的数据
                var data = db.Order.Where(x => x.BookId == id).Join(db.Book, s => s.BookId, j => j.BookId, (s, j) => new
                {
                    Address = s.Address,
                    Num = s.Num,
                    OrderId = s.OrderId,
                    BookId = j.BookId,
                    AuthorName = j.AuthorName,
                    Title = j.Title
                });

                ViewBag.Data = data;
                return View();//根据图书id查找订单

读取数据

 @foreach (var i in ViewBag.Data)
            {
            <tr>

                <td>@i.GetType().GetProperty("Title").GetValue(i, null)</td>
                <td>@i.GetType().GetProperty("AuthorName").GetValue(i, null)</td>
                <td>@i.GetType().GetProperty("Address").GetValue(i, null)</td>
                <td>@i.GetType().GetProperty("Num").GetValue(i, null)</td>
                <td>
                    <a onclick="del(this)">删除</a>
                </td>
            </tr>
            }

var model = db.Database.SqlQuery<类>("exec 存储过程名字 @type,@town,@village", paras).ToList(); 执行存储过程

var data = db.Database.SqlQuery< 类 >("select * ..... ").ToList();执行sql语句

var x = db.Database.ExecuteSqlCommand("delete from AAA where id in (select id from BBB where ); 执行语句返回受影响的行数

Join 语句

use my_temp

CREATE TABLE [dbo].[A](
     [A_Id] [int] IDENTITY(1,1) primary key NOT NULL, --自增1;从1开始 设置为主键
     [B_Id] [int] NOT NULL, --外键=B表主键
     [C_Id] [int] NOT NULL, --外键=C表主键
     [A_type] [nvarchar](50) NOT NULL, --类型
)
CREATE TABLE [dbo].[B](
     [B_Id] [int] IDENTITY(1,1) primary key NOT NULL, --自增1;从1开始 设置为主键
     [B_type] [nvarchar](50) NOT NULL, --类型
)
CREATE TABLE [dbo].[C](
     [C_Id] [int] IDENTITY(1,1) primary key NOT NULL, --自增1;从1开始 设置为主键
     [C_type] [nvarchar](50) NOT NULL, --类型
)
--插入语句  A表数据
INSERT INTO [dbo].[A]([B_Id],[C_Id],[A_type])VALUES(1,1,'A类型')
INSERT INTO [dbo].[A]([B_Id],[C_Id],[A_type])VALUES(2,2,'A类型1')
INSERT INTO [dbo].[A]([B_Id],[C_Id],[A_type])VALUES(3,3,'A类型2')
select * from A
--插入B表
INSERT INTO [dbo].[B]([B_type])VALUES('B类型')
INSERT INTO [dbo].[B]([B_type])VALUES('B类型1')
INSERT INTO [dbo].[B]([B_type])VALUES('B类型2')
--插入C表
INSERT INTO [dbo].[C]([C_type])VALUES('C类型')
INSERT INTO [dbo].[C]([C_type])VALUES('C类型1')
INSERT INTO [dbo].[C]([C_type])VALUES('C类型2')

--join 查询; 两表 join 查询
SELECT
 *
FROM A
LEFT JOIN B 
ON
A.B_Id=B.B_Id;

Join 语句

--join 查询; 三表 join 查询
SELECT
 *
FROM A
    
    LEFT JOIN B 
    ON
    A.B_Id=B.B_Id
    LEFT JOIN C
    ON
    A.A_Id= C.C_Id;

Join 语句

--其他关系

Join 语句

以上是 AA表　　BB表　　CC表;

1.查询 BB表中AA_Id数量和 BB_NUM的和

语句

select AA_Id, COUNT(AA_Id),SUM(BB_NUM)BB_NUM from BB  where 1=1
group by AA_Id  --以AA_Id分组

结果

Join 语句

2.再把结果与AA表关联

语句

select AA.AA_Id,F.数量,F.BB_NUM from AA 
left join 
(select AA_Id, COUNT(AA_Id)数量,SUM(BB_NUM)BB_NUM from BB  where 1=1 group by AA_Id ) F 
on  
AA.AA_Id=F.AA_Id

结果

Join 语句

查询 CC表中AA_Id数量和 CC_NUM的和

--查询 CC表中
select AA_Id, COUNT(AA_Id),SUM(CC_NUM)CC_NUM from CC  where 1=1 group by AA_Id

Join 语句

三表关联

Join 语句

语句

select AA.AA_Id,F.ID数量,F.BB_NUM,G.ID数量,G.CC_NUM from AA 
left join 
(select AA_Id, COUNT(AA_Id)ID数量,SUM(BB_NUM)BB_NUM from BB  where 1=1 group by AA_Id ) F 
on  
AA.AA_Id = F.AA_Id
left join 
(select AA_Id, COUNT(AA_Id)ID数量,SUM(CC_NUM)CC_NUM from CC  where 1=1 group by AA_Id ) G
on
AA.AA_Id = G.AA_Id

语句

use my_temp

CREATE TABLE [dbo].[AA](
     [AA_Id] [int] IDENTITY(1,1) primary key NOT NULL, --自增1;从1开始 设置为主键
     [AA_type] [nvarchar](50) NOT NULL, --类型
)
CREATE TABLE [dbo].[BB](
     [BB_Id] [int] IDENTITY(1,1) primary key NOT NULL, --自增1;从1开始 设置为主键
     [AA_Id] [int] NOT NULL,
     [BB_NUM][int] NOT NULL, --数量
)
CREATE TABLE [dbo].[CC](
     [CC_Id] [int] IDENTITY(1,1) primary key NOT NULL, --自增1;从1开始 设置为主键
     [AA_Id] [int] NOT NULL,
     [CC_NUM][int] NOT NULL, --数量
)
--END

--插入语句  AA表数据
--INSERT INTO [dbo].[AA]([AA_type])VALUES('1类型')
--INSERT INTO [dbo].[AA]([AA_type])VALUES('2类型')
--INSERT INTO [dbo].[AA]([AA_type])VALUES('3类型')
--INSERT INTO [dbo].[AA]([AA_type])VALUES('4类型')
--INSERT INTO [dbo].[AA]([AA_type])VALUES('5类型')
--END
--插入B表
--INSERT INTO [dbo].[BB]([AA_Id],[BB_NUM])VALUES('1',5)
--INSERT INTO [dbo].[BB]([AA_Id],[BB_NUM])VALUES('1',4)
--INSERT INTO [dbo].[BB]([AA_Id],[BB_NUM])VALUES('2',3)
--INSERT INTO [dbo].[BB]([AA_Id],[BB_NUM])VALUES('2',2)
--INSERT INTO [dbo].[BB]([AA_Id],[BB_NUM])VALUES('3',1)
--END
--插入CC表
--INSERT INTO [dbo].[CC]([AA_Id],[CC_NUM])VALUES('1',5)
--INSERT INTO [dbo].[CC]([AA_Id],[CC_NUM])VALUES('2',4)
--INSERT INTO [dbo].[CC]([AA_Id],[CC_NUM])VALUES('2',3)
--INSERT INTO [dbo].[CC]([AA_Id],[CC_NUM])VALUES('2',2)
--INSERT INTO [dbo].[CC]([AA_Id],[CC_NUM])VALUES('3',1)
--END

--查询 AA 表中数据
select * from AA 
select * from BB 
select * from CC

其他

Join 语句

第一个表 join 第二个表

Join 语句

语句

select * from (
select A_Id,A_type from A where A_Id=1 
)A
Left join
(select top 2 AA_Id,BB_NUM from BB)C
on
A.A_Id = C.AA_Id

父数据有时间,子数据无时间

select * from Temp

Join 语句

--获得最新时间 A 类型的价格

select * from(
    select 类型,max(时间) as 最新时间 from temp group by 类型
)A
left Join
(
    select * from Temp
)B
ON
A.最新时间 = B.时间

---------------------------------------- 查询重复数量

         select * from (  select 工号,count(*) AS 数量 from (
                    select 工号 ,姓名   from  dbo.Base移动用户
                  )C
                  group by  工号 )D
                  where  数量>1

相关推荐