如何在两个字段之间使用减号运算符减减 [英] How do I use Minus operator to Minus between 2 fields

问题描述

我坚持这个我写SQL来显示产品数量

例子

我需要它们这样显示



我有12件产品

我在Textbox1上显示它们作为所有产品

然后第二个Textbox我需要显示已注册产品（注册产品从二进制0 - 1开始计算，0 =批准1 =未批准）

喜欢这样





所有产品= 12

注册= 6（所有产品（12） - 注册商品（6）



然后它会将产品扩展到这样的类别



食物：2

使用：2

TAILOR： 2



现在我只能展示

所有产品然后将12个产品扩展到一个类别

i'我坚持使用



1.我可以通过存储过程（T-SQL）计算注册产品

2.我可以扩展吗？他们属于一个类别



我有一个迭代一个代码来显示它们这样

 选择计数（T 。[ProductName]） as  ProductCount，
 T.CategoryID，
 T.estbProducerID，
 T.NewProducer，
 T. [ProductCountAll] 
 来自（
  select  tn。[ProductName]， 
 tn。[CategoryID]，
 tp.estbProducerID，
 tp.NewProducer，
 count（*） over （ ） as  [ProductCountAll] 
 来自 [tpdcn] tp，[tpdtn] tn 
  left   join  [tpdcn] 
  on  tn.parentid = tpdcn.libDocumentID 
 其中 tp.libdocumentID =  @ id  
） as  T 
  group   by  T. [CategoryID]，
 T.estbProducerID，T.NewProducer，T。[ProductCountAll] 
 
 
 
  END  

它的节目

 ProductCount类别ID estbProducerID NewProducer ProductCountAll 
 4 1 2 1 12 
 3 2 2 1 12 
 2 3 2 1 12 
 2 4 2 1 12 
 1 5 2 1 12 

 FOOD = 4 
 TAILOR = 3 
 USES = 2 
 EX1 = 2 
 Ex2 = 2 
 ex3 = 1 

解决方案

  USE  [MyDatabase] 
  GO  
  / *   *****对象：StoredProcedure [dbo]。[sp_1]脚本日期：03/17/2014 22:07:22 ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   OFF  
  GO  
 创建  PROCEDURE  [dbo]。[sp_1] 
 
  as  
 开始 
 声明  @ AllProducts   int  
 声明  @ RegProducts   int  
 声明  @ UnregProducts  < span class =code-keyword> int  
 
 选择  @ AllProducts  =（选择 COUNT（NewProducer） as  Allproducts 来自 TN）
 选择  @ RegProducts  =（选择 COUNT（NewProducer） 从 TN  code-keyword> where  newproducer = 0）
 选择  @ UnregProducts  = （选择 COUNT（NewProducer）作为 UnRegproducts 来自 TN 其中 newproducer = 1）
 
 选择  @ AllProducts   as  AllProducts， @ RegProducts   as  RegProducts， @ UnRegProducts   as  UnRegProducts 
 结束 

 选择 a.cat_name，COUNT（b.categoryid）  tot 来自 t_cat a，tn b 其中 a.categoryid = b.categoryid  group   by  a.cat_name  order   by  tot  desc  

如果你想计算在文本框上。请按照此方法

 我 .textbox3.text = val（  me  .textbox1.text） -  val（ me  .textbox2.text）

I stuck on this I write SQL to show a count of product
Example
I need them to show like this

I have 12 Pieces of Product
I show them on "Textbox1" as all product
then the second "Textbox" I need to show "Registered Product" (Registered Product is count from binary 0 - 1 that is 0 = Approved 1 = not approved)
like this


All Product = 12
Registered = 6 (All Product(12) - Registered Product(6)

Then it will expanded the product to a category like this

FOOD : 2
USES : 2
TAILOR : 2

now I just can show the
All product then expanded 12 products to a category
i'm stuck on

1.how can I count a registered product by Stored procedures (T-SQL)
2.how can I expanded them to a category

I had write a code to show them like this

select count(T.[ProductName]) as ProductCount,
       T.CategoryID,
       T.estbProducerID,
       T.NewProducer,
       T.[ProductCountAll]
from (
     select tn.[ProductName],
            tn.[CategoryID],
            tp.estbProducerID,
            tp.NewProducer,
            count(*) over() as [ProductCountAll]
     from [tpdcn] tp,[tpdtn] tn
       left join [tpdcn]
         on tn.parentid = tpdcn.libDocumentID
     where tp.libdocumentID = @id
     ) as T
group by T.[CategoryID],
         T.estbProducerID,T.NewProducer,  T.[ProductCountAll]
		
	

END

it's show

ProductCount	CategoryID	estbProducerID	NewProducer	ProductCountAll
      4	             1	               2	     1	               12
      3	             2	               2	     1	               12
      2	             3	               2	     1	               12
      2        	     4	               2	     1	               12
      1	             5	               2	     1	               12

FOOD = 4
TAILOR = 3
USES = 2
EX1 = 2
Ex2 = 2
ex3 = 1

解决方案

USE [MyDatabase] 
GO
/****** Object:  StoredProcedure [dbo].[sp_1]    Script Date: 03/17/2014 22:07:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_1]

as
Begin
Declare @AllProducts int
Declare @RegProducts int
Declare @UnregProducts int

Select @AllProducts = (Select COUNT(NewProducer) as Allproducts from TN)
Select @RegProducts = (select COUNT(NewProducer) as Regproducts from TN where newproducer=0)
Select @UnregProducts = (select COUNT(NewProducer) as UnRegproducts from TN where newproducer=1)

Select  @AllProducts as AllProducts, @RegProducts as RegProducts,@UnRegProducts as UnRegProducts
end

select a.cat_name,COUNT(b.categoryid) as tot from t_cat a, tn b where a.categoryid=b.categoryid group by a.cat_name order by tot desc

If you want to calculate on Textbox. Please Follow this method

Me.textbox3.text = val(me.textbox1.text)- val(me.textbox2.text)

这篇关于如何在两个字段之间使用减号运算符减减的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！