34,590
社区成员
发帖
与我相关
我的任务
分享
--借了Tony哥的测试数据
IF NOT OBJECT_ID('[Company]') IS NULL
DROP TABLE [Company]
GO
CREATE TABLE [Company]([CompanyName] NVARCHAR(10),[CompanyId] INT)
INSERT [Company]
SELECT 'a',1 UNION ALL
SELECT 'b',2
GO
--SELECT * FROM [Company]
--> 生成测试数据表:Product
IF NOT OBJECT_ID('[Product]') IS NULL
DROP TABLE [Product]
GO
CREATE TABLE [Product]([ProductId] NVARCHAR(10),[CompanyId] INT,[ProductName] NVARCHAR(10))
INSERT [Product]
SELECT 'p1',1,'pname1' UNION ALL
SELECT 'p2',1,'pname2' UNION ALL
SELECT 'p3',1,'pname3' UNION ALL
SELECT 'p1',2,'pname1' UNION ALL
SELECT 'p2',2,'pname2' UNION ALL
SELECT 'p3',2,'pname3'
GO
--SELECT * FROM [Product]
--取最小产品记录
select
*
from
product a
where
productid = (select min(productid) from product where companyid = a.companyid)
--结果
/*
ProductId CompanyId ProductName
---------- ----------- -----------
p1 1 pname1
p1 2 pname1
(2 行受影响)
*/
--取最小产品记录
select
*
from
product a
where
productid = (select max(productid) from product where companyid = a.companyid)
--结果
/*
ProductId CompanyId ProductName
---------- ----------- -----------
p3 2 pname3
p3 1 pname3
(2 行受影响)
*/
--取随机一条记录
select
*
from
product a
where
productid = (select top 1 productid from product where companyid = a.companyid order by newid())
--结果
/*
ProductId CompanyId ProductName
---------- ----------- -----------
p3 1 pname3
p2 2 pname2
(2 行受影响)
*/
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-04 14:39:23
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:Company
IF NOT OBJECT_ID('[Company]') IS NULL
DROP TABLE [Company]
GO
CREATE TABLE [Company]([CompanyName] NVARCHAR(10),[CompanyId] INT)
INSERT [Company]
SELECT 'a',1 UNION ALL
SELECT 'b',2
GO
--SELECT * FROM [Company]
--> 生成测试数据表:Product
IF NOT OBJECT_ID('[Product]') IS NULL
DROP TABLE [Product]
GO
CREATE TABLE [Product]([ProductId] NVARCHAR(10),[CompanyId] INT,[ProductName] NVARCHAR(10))
INSERT [Product]
SELECT 'p1',1,'pname1' UNION ALL
SELECT 'p2',1,'pname2' UNION ALL
SELECT 'p3',1,'pname3' UNION ALL
SELECT 'p1',2,'pname1' UNION ALL
SELECT 'p2',2,'pname2' UNION ALL
SELECT 'p3',2,'pname3'
GO
--SELECT * FROM [Product]
-->SQL查询如下:
--法一:
SELECT A.[COMPANYNAME],B.[PRODUCTID],B.[PRODUCTNAME]
FROM [COMPANY] A
JOIN [PRODUCT] B
ON A.COMPANYID=B.COMPANYID
WHERE B.[PRODUCTID] =(
SELECT MIN([PRODUCTID])
FROM [PRODUCT]
WHERE COMPANYID=B.COMPANYID)
--法二:
SELECT A.[COMPANYNAME],B.[PRODUCTID],B.[PRODUCTNAME]
FROM [COMPANY] A
JOIN [PRODUCT] B
ON A.COMPANYID=B.COMPANYID
WHERE NOT EXISTS(
SELECT 1
FROM [PRODUCT]
WHERE COMPANYID=B.COMPANYID
AND [PRODUCTID]<B.[PRODUCTID])
/*
COMPANYNAME PRODUCTID PRODUCTNAME
----------- ---------- -----------
a p1 pname1
b p1 pname1
(2 行受影响)
*/
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-04 14:39:23
-- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:Company
IF NOT OBJECT_ID('[Company]') IS NULL
DROP TABLE [Company]
GO
CREATE TABLE [Company]([CompanyName] NVARCHAR(10),[CompanyId] INT)
INSERT [Company]
SELECT 'a',1 UNION ALL
SELECT 'b',2
GO
--SELECT * FROM [Company]
--> 生成测试数据表:Product
IF NOT OBJECT_ID('[Product]') IS NULL
DROP TABLE [Product]
GO
CREATE TABLE [Product]([ProductId] NVARCHAR(10),[CompanyId] INT,[ProductName] NVARCHAR(10))
INSERT [Product]
SELECT 'p1',1,'pname1' UNION ALL
SELECT 'p2',1,'pname2' UNION ALL
SELECT 'p3',1,'pname3' UNION ALL
SELECT 'p1',2,'pname1' UNION ALL
SELECT 'p2',2,'pname2' UNION ALL
SELECT 'p3',2,'pname3'
GO
--SELECT * FROM [Product]
-->SQL查询如下:
select case when [ProductId]=(select MIN([ProductId]) from [Product] where [CompanyId]=t.[CompanyId])
then (select [CompanyName] from [Company] where [CompanyId]=t.[CompanyId])
else ''
end as [CompanyName],
[ProductId],[ProductName]
from [Product] t
/*
CompanyName ProductId ProductName
----------- ---------- -----------
a p1 pname1
p2 pname2
p3 pname3
b p1 pname1
p2 pname2
p3 pname3
(6 行受影响)
*/
楼主该不会是这个意思吧select
*
from
Company a, Product b
where
a.CompanyId=b.CompanyId
and
b.CompanyId=(select max(CompanyId) from Product where ProductId=b.ProductId)