Sql语句使用

1.按时间倒叙

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [BlockId]
      ,[BlockchainFileId]
      ,[BlockVersion]
      ,[BlockHash]
      ,[PreviousBlockHash]
      ,[BlockTimestamp]
  FROM [bitcoin].[dbo].[Block] order by [BlockTimestamp] desc

2.本地解析了多少bat文件。

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [BlockchainFileId]
      ,[BlockchainFileName]
  FROM [bitcoin].[dbo].[BlockchainFile] order by [BlockchainFileId] desc

共728个文件。

3.727.dat文件里包含多少个区块？

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [BlockId]
      ,[BlockchainFileId]
      ,[BlockVersion]
      ,[BlockHash]
      ,[PreviousBlockHash]
      ,[BlockTimestamp]
  FROM [bitcoin].[dbo].[Block]
  where [BlockchainFileId]=727 

从BlockId：446230至446256共27个区块。

4.区块号为446256，的交易ID为？

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [BitcoinTransactionId]
      ,[BlockId]
      ,[TransactionHash]
      ,[TransactionVersion]
      ,[TransactionLockTime]
  FROM [bitcoin].[dbo].[BitcoinTransaction]
  where [BlockId]>=446256 order by [BitcoinTransactionId] asc

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [BitcoinTransactionId]
      ,[BlockId]
      ,[TransactionHash]
      ,[TransactionVersion]
      ,[TransactionLockTime]
  FROM [bitcoin].[dbo].[BitcoinTransaction]
  where [BlockId]=446230 order by [BitcoinTransactionId] desc

区块号：446256，BitcoinTransactionId：184101328-184101107
区块号：446230，BitcoinTransactionId：184051742-184054233

5.在交易记录表中，查找在第四步骤中的交易ID范围内，且源输出交易为空的所有交易。

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [TransactionInputId]
      ,[BitcoinTransactionId]
      ,[SourceTransactionOutputId]
  FROM [bitcoin].[dbo].[TransactionInput]
  where [BitcoinTransactionId]>=184051742 and [SourceTransactionOutputId] is null

一共27笔交易满足条件。
6.查询coinbase交易的outscript字段。

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [TransactionOutputId]
      ,[BitcoinTransactionId]
      ,[OutputIndex]
      ,[OutputValueBtc]
      ,[OutputScript]
  FROM [bitcoin].[dbo].[TransactionOutput]
  where  exists (SELECT  [BitcoinTransactionId]  FROM [bitcoin].[dbo].[TransactionInput] where [BitcoinTransactionId]>=184051742 and [SourceTransactionOutputId] is null)

先对外部做select查询，然后外部select结果再去匹配exists中的条件，符合条件的就是需要的记录。