How to write this SQL query?

Category: MS-SQL Server -> Applications Author: wangyuxi112 Date: 2001-07-08 13:13:28
 
wangyuxi112
2001-07-08 13:13:28
/ *
Known table test1
Product number Packaging Specification (KG / barrel) Production Quantity (KG)
001 2 11.5
002 ; 5 30
003 10 102

from the table test1 through SQL statement to query the following results (rounding rules for the production quantity has a decimal fraction removed then +1)
this SQL query how to write?

Product number Packaging Specification (KG / barrel) Production Quantity (KG) number of filled buckets the number of mantissa mantissa barrel barrels loaded quantity (KG) Total number of barrels produced rounded
001 ; 2 11.5 5 1 1.5 ; 6 12
002 530 6 0 0 6 30
003 10102 10 12 11102
* /

- create a test environment
if exists (select * from sysobjects where id = object_id ( N'test1 ') and OBJECTPROPERTY (id, N'IsUserTable') = 1)
begin
drop table test1
end

CREATE TABLE [test1] (
[Product Code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Packing specification (KG / barrel)] [float] NULL,
[production quantity (KG)] [float] NULL
) ON [ PRIMARY]
GO

Insert test1 ([Product Code], [Packing specification (KG / barrel)], [production quantity (KG)]) Values ​​( '001 ', 2,11.5)
Insert test1 ([Product Code], [Packing specification (KG / barrel)], [production quantity (KG)]) Values ​​( ; '002 ', 5,30)
Insert test1 ([Product Code], [Packing specification (KG / barrel)], [production quantity (KG)]) Values ​​( '003 ', 10,102)
GO
Select * from Test1

- Remove the test environment
if exists (select * from sysobjects where id = object_id (N'test1 ') and OBJECTPROPERTY (id, N'IsUserTable') = 1)
begin
drop table test1
end

ccwangcheng
2001-07-08 13:25:45
if exists (select * from sysobjects where id = object_id (N'test1 ') and OBJECTPROPERTY (id, N'IsUserTable') = 1)
begin
drop table test1
end

CREATE TABLE [test1] (
[Product Code] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL,
[Packing (KG / barrel)] [float] NULL,
[production quantity (KG)] [float] NULL
) ON [PRIMARY]
GO

Insert test1 ([Product Code], [Packing specification (KG / barrel)], [production quantity (KG)]) Values ​​('001 ', 2,11.5)
Insert test1 ([Product Code], [Packing specification (KG / barrel)], [production quantity (KG)]) Values ​​( '002 ', 5,30)
Insert test1 ([Product Code], [Packing specification (KG / barrel)], [production quantity (KG)]) Values ​​(' 003 ', 10,102)
GO
SELECT a. *,
filled bucket number = a. [production quantity (KG)] / a. [Packing specification (KG / barrel)],
mantissa bucket number = CASE WHEN CAST (a. [production quantity (KG)] AS DECIMAL (18,2))% cast (a. [Packing specification ( KG / barrel)] AS DECIMAL (18,2)) = 0 THEN 0 ELSE 1 END,
[mantissa barrels loaded quantity (KG)] = CAST (a. [production quantity (KG)] AS DECIMAL (18,2))% cast (a. [Packing (KG / barrel)] AS DECIMAL (18,2)),
the total number of barrels = a. [production quantity (KG)] / a. [Packing specification (KG / barrel)] + (CASE WHEN CAST (a. [production quantity (KG)] AS ; DECIMAL (18,2))% cast (a. [Packing (KG / barrel)] AS DECIMAL (18,2)) = 0 THEN 0 ELSE 1 END ),
production quantity rounded = ceiling ([production quantity (KG)])
FROM test1 a
/ *
Product number Packaging Specification (KG / barrel) Production Quantity (KG) number of barrels filled with the number of mantissa mantissa barrel barrels loaded quantity (KG) the total number of barrels produced rounded
- -------------------------------------------- ------------------------------------------ ----------
001 2 11.5 5.75 1 1.5 ; 6.75 12
002 ; 530 ; 600 ; 6 ; 30
003 10102 10.2 ; 12 ; 11.2 102 * /
tc_knight
2001-07-08 13:34:38
Select *, 
[ (KG)] = CASE WHEN (CHARINDEX('.', CAST([ (KG)] AS VARCHAR(100))) > 0) THEN (CAST([ (KG)] AS INT)+1) ELSE [ (KG)] END
from Test1
fanhuazangai
2001-07-08 13:44:44
rookie asked this sql in sqlserver2005 have a field name (varchar) Table table_1
; ; 13-5
13-4
; 13-9
13-7
sort how to write. .
I wrote a front row can not be excluded either after. Very depressed, I was written this way.

select name from table_1 order by convert (int, charIndex ('-', name) -1), convert (int, Stuff (name, 1 , charIndex ('-', name),''))
zhong133245
2001-07-08 13:48:43
rookie asked this sql in sqlserver2005 have a field name (varchar) Table table_1
13-5
13-4
14-9
14-7
sort how to write. .
I wrote a front row can not be excluded either after. Very depressed, I was written this way.

select name from table_1 order by convert (int, charIndex ('-', name) -1), convert (int, Stuff (name, 1, charIndex (' - ', name),''))

is this. . . .