MSSQL排序语句例子

–MSSQL排序语句例子,可用于验证平板商品排序等情况。

选出需要的字段

select Products.BarCode,Products.UserCode,Products.Discount,Products.UnitsInStock,Products.ProductName,iif(Categories.CategoryName is null, ‘sin familia’,Categories.CategoryName) as CategoryName,Products.UnitPrice,Products.UnitPrice2,Products.Picture,Products.QuantityPerUnit,Products.QuantityPerUnit2nd,Categories.type1,Products.MarketingNO

FROM Products LEFT JOIN Categories     ON Products.CategoryID = Categories.CategoryID

条件状态为正常 0 或者 7.状态代码在商品替换状态界面查询

where (Products.MarketingNO =’0′ or Products.MarketingNO =’7′)   

排序类型

ORDER BY

首先按类别注释排序。其中当注释为空排序类别名称

CASE len(cast(Categories.Description as NVARCHAR(max)))

WHEN null THEN Categories.CategoryName

WHEN 0 THEN Categories.CategoryName

ELSE cast(Categories.Description as NVARCHAR(max))

END,

第二排序商品名称2

Products.ProductName2,

第三排序号商品条码例如排序商品编号这句改为 Products.UserCode

Products.BarCode

直接丢进去执行注释可以不理。

IBEE税解决方案

  1. 升级到最新2017测试版
  2. 下载IBEE发票格式
  3. 自定义发票格式勾选23.打印价格8(ibee)
  4. 商品的ibee税填写到商品信息,更多里面的价格8。注:商品售价填写含ibee的价格。因为订单出的价格就是含税含ibee价格,价格8填写的为该商品的计算出来的ibee价格数。
  5. enjoy!

手工修改报表格式步骤:

  1. 打开发票报表,查找 <Field Name=”TaxC”>改为如下
  2. <Field Name=”TaxC”>
    <DataField>TaxC</DataField> <rd:TypeName>System.Decimal</rd:TypeName>
    </Field>
    <Field Name=”PriceEx”>
    <DataField>PriceEx</DataField> <rd:TypeName>System.Decimal</rd:TypeName>
    </Field>
  3. <Field Name=”PriceEx”>
    <DataField>PriceEx</DataField> <rd:TypeName>System.Decimal</rd:TypeName>
    </Field>
    这段为添加内容。
  4. 表体ibee为此变量

    =Fields!PriceEx.Value

    表尾合计字段表达式为

    =Sum(Fields!PriceEx.Value, “ds_shopDataSet_Order_Details_Ticket”)

  5. 完整高级班表达式表体ibee

    =iif(Fields!PriceEx.Value<>0,FormatNumber(Fields!PriceEx.Value ,cint(Mid(Parameters!Albformat.Value,13,1))),“”)

    表尾合计

    =iif(Sum(Fields!PriceEx.Value, “ds_shopDataSet_Order_Details_Ticket”)<>0,iif(cint(Parameters!FormatNumber_SaleDiamelBits.Value)>0,FormatNumber(Sum(Fields!PriceEx.Value, “ds_shopDataSet_Order_Details_Ticket”),cint(Parameters!FormatNumber_SaleDiamelBits.Value)),FormatNumber(Sum(Fields!PriceEx.Value, “ds_shopDataSet_Order_Details_Ticket”),2)),“”)

 

 

高级SQL报表语句

SELECT TOP (100) PERCENT [Order Details].ProductID, SUM([Order Details].Quantity) AS QuantityTotal, [Order Details].UnitPrice,
(CASE WHEN Products.UnitPrice8 IS NULL THEN 0 ELSE Products.UnitPrice8 END) AS PriceEx,
[Order Details].UserCode, [Order Details].BarCode, [Order Details].ProductName, [Order Details].QuantityPerUnit2nd,
[Order Details].QuantityPerUnit, [Order Details].Discount, [Order Details].Tax, SUM([Order Details].PriceBase)
AS PriceBase, SUM([Order Details].PriceIncTax) AS PriceIncTax, SUM([Order Details].TaxC) AS TaxC
FROM [Order Details] RIGHT OUTER JOIN
Products ON [Order Details].ProductID = Products.ProductID
WHERE ([Order Details].OrderID = OID)
GROUP BY [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].UserCode, [Order Details].BarCode,
[Order Details].ProductName, [Order Details].Discount, [Order Details].QuantityPerUnit2nd,
[Order Details].QuantityPerUnit, [Order Details].Tax, Products.UnitPrice8
ORDER BY [Order Details].UserCode