~~~~~~~~~一句Sql,马上给分？~~~~~~~
Tabel A：
name b_type qty
外套画报 B 10
外套画报 C 5
Tabel B:
id b_type
1 A
2 B
3 C
4 D
需要结果：
name b_type qty percent
外套画报 A 0 0%
外套画报 B 10 67%
外套画报 C 5 33%
外套画报 D 0 0%
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-10 09:49:43
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
--Jun 10 2013 20:09:10
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[TabelA]
if object_id('[TabelA]') is not null drop table [TabelA]
go
create table [TabelA]([name] varchar(8),[b_type] varchar(1),[qty] int)
insert [TabelA]
select '外套画报','B',10 union all
select '外套画报','C',5
--> 测试数据：[TabelB]
if object_id('[TabelB]') is not null drop table [TabelB]
go
create table [TabelB]([id] int,[b_type] varchar(1))
insert [TabelB]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'
--------------开始查询--------------------------
select '外套画报',b.b_type,b.*,SUBSTRING(CONVERT(VARCHAR(20),(CASE WHEN a.qty IS NULL THEN 0 ELSE qty END )*1.0/(SELECT SUM(qty)qty FROM [TabelA] )*100),1,4)+'%'
from [TabelA] a FULL JOIN [TabelB] b ON a.b_type=b.b_type
ORDER BY id
----------------结果----------------------------
/*
b_type id b_type
-------- ------ ----------- ------ ---------
外套画报 A 1 A 0.00%
外套画报 B 2 B 66.6%
外套画报 C 3 C 33.3%
外套画报 D 4 D 0.00%
*/
----------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-09-10 09:57:04
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
--Apr 2 2010 15:53:02
--Copyright (c) Microsoft Corporation
--Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------
--> 测试数据：[TabelA]
if object_id('[TabelA]') is not null drop table [TabelA]
go
create table [TabelA]([name] varchar(8),[b_type] varchar(1),[qty] int)
insert [TabelA]
select '外套画报','B',10 union all
select '外套画报','C',5
--> 测试数据：[TabelB]
if object_id('[TabelB]') is not null drop table [TabelB]
go
create table [TabelB]([id] int,[b_type] varchar(1))
insert [TabelB]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'
--------------开始查询--------------------------
SELECT
ISNULL(A.NAME,'外套画报') AS NAME,B.[b_type],ISNULL(A.qty,0) AS qty,
ISNULL(LTRIM(CAST( A.qty*1.0/(SELECT SUM(QTY) FROM TabelA)*100 AS DEC(18,0)))+'%','0%') AS [percent]
FROM
TabelA A
FULL JOIN
TabelB B
ON
A.b_type=B.b_type
ORDER BY
B.b_type
----------------结果----------------------------
/* NAME b_type qty percent
-------- ------ ----------- ------------------------------------------
外套画报 A 0 0%
外套画报 B 10 67%
外套画报 C 5 33%
外套画报 D 0 0%
(4 行受影响)
*/
